I have a table EMP having columns User_Id, ENO,Org_ID, Dept_ID.
Now I would like to insert values into this EMP table using below conditions.
Insert into EMP(user_seq.nextval,
(select empno from employees where empno in(....(empnumbers),
(select org_id from organizations where org_name='XXXXXXXXXX'),
(select dept_id from DEPT where dname in ('MANAGER','ANALYST','SALESMAN') ))
Please provide provide me the query for the above requirement.
tell you what ..
forget the INSERT .. seriously.
Write a single SQL statement that returns what you want to see in the target table:
select user_seq.nextval, e.empno, o.org_id, d.deptid from employeees e, organizations o, dept d where e.dept_id = d.dept_id and e.org_id = o.org_id;
if you can do that - (and thereby showing us how your tables are related).
We'll happily show you how to insert that data into that table
As it stands, however, we have no idea what your tables look like, how they related, nothing ... so we can't help.
I still don't see the problem:
insert into emp
from employees e
, organizations o
, dept t
where <joins go here>
and e.empno in (empnumbers)
and o.org_name = 'XXXXXXXXXX')
and d.dname in ('MANAGER','ANALYST','SALESMAN');
Think in sets and joins, databases are born to join and crunch datasets...
we can write joins but we are mapping the records and retrieving only that particular records.. so how can I retrieve obly that particular records?
Like so many other things, that depends on your data and your requirements. Unless you say where you're coming from and where you want to go, you can't expect anyone to give you very good directions.
Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
If you're asking about a DML statement, such as INSERT, the CREATE TABLE and INSERT statementsyou post should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
Always say which version of Oracle you're using (for example, 184.108.40.206.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Create a small sample test case.
Fake data, fake tables (similar enough to your own so you can relate).
Once that's done, again, I'll go back to my request to you: Forget updates, and inserts. Write a single SQL that returns the end result that you want.
Once you have that, stuffing it into a table (via INSERT or UPDATE is trivial).
And what you'll usually find - is generating that initial SQL is not very hard as well
Please correct the following code..
for i in (select div_id from div, groups where DIV.DIV_CODE=groups.group_name)
update groups set group_id=i.div_id;
No kidding: without knowing what you want to do, it's very hard to say how to do it. Post some sample data (CREATE TABLE and INSERT statements for all tables involved, as they are before the UPDATE) and the results you want from that data (i. e., the contents of than changed table after the UPDATE).
The code above is updating every row in the groups table over and over again. Every time, it sets groupd_id to the same value for all rows. Since there is no order to the query that governs the loop, it is arbitrary which one will be done last, which is the value that all rows will have. I'll bet that's not what you want, but as long as I don't know what you do want, I can't say how to do it.
We're back to:
"Please set up a simple example test case" so we can help.
Without something we can run at our end, we can't help you ..
Did you read the link Frank posted?
You want us to take the time to solve your problem, but you don't want to take the time to post a complete question?
Yeah, seems fair