need help in update statement

[rneel]

i have the following structure table a1
NO DEPT
--------- ----------
1 2
1 3
1 4
1
table a2 has the following structure
NO DEPT
--------- ----------
1

I need to update a2 so the at the end a2 has the same set or rows as a1... i get an error -- single subquery returns more than one row... need help
thanks
r-neel

[chandu031]

Hi,

Looking at your tables , i dont get why you want to update the table. If you want all the rows of a1 in a2 where no = 1, then you will have to insert new rows. An UPDATE can't insert new rows into a table, it will update existing row or rows.
If you run an update on a2 , it will obviously fail as there are multiple matches for no = 1 with a1 table. This is what I could gather from the information that you have given. If you are trying to do something else , then with more information I may be able to answer it...

[masdi2t]

i have the following structure table a1
NO DEPT
--------- ----------
1 2
1 3
1 4
1
table a2 has the following structure
NO DEPT
--------- ----------
1

I need to update a2 so the at the end a2 has the same set or rows as a1... i get an error -- single subquery returns more than one row... need help
thanks
r-neel

use MERGE instead of UPDATE

[debasisdas]

U can use MERGE command instead of UPDATE ,
but only if u r using oracle 9i or above
because it is not supported in prior versions.


simple ex.

merge into emp5 e
using (select empno,ename from emp) e1
on (e.empno=e1.emp no)
when matched then
update set
e.ename=e1.enam e
when not matched then
insert values(e1.empno ,e1.ename)