Re: Oracle SQL: UPDATE using Subquery or Join

Hallo John,

I can't understand your error message. This was Dimitiris statement, not from me.

> Thanks for the reply Ralf. I tried your suggestion but it failed with
> the following error:
>
> ******************************
> update a set name=(select name from b) where exists (select id from b
> where id=a.id)
> *
> ERROR at line 1:
> ORA-01427: single-row subquery returns more than one row
> ******************************

scott_at_TDB10> select * from a;

        ID NAME
         1 OldName1
         2 OldName2
         3 OldName3
         4 OldName4

scott_at_TDB10> select * from b;
        ID NAME
         2 NewName2
         4 NewName4
         6 NewName6

scott_at_TDB10> update (select a.name a_name,b.name b_name from a,b where a.id=b.id)   2 set a_name=b_name;

2 Zeilen wurden aktualisiert.

scott_at_TDB10> select * from a;

        ID NAME
         1 OldName1
         2 NewName2
         3 OldName3
         4 NewName4

Correct, or not?

regards

Ralf Received on Wed May 28 2003 - 07:20:40 CEST