Oracle SQL: UPDATE using Subquery or Join

Sorry Ralf - I miscopied your solution incorrectly. Your solution indeed works like a champ. Thank you very much! It was a great help.

db.2.gemini_at_spamgourmet.com (Ralf) wrote in message news:<51a9e702.0305272120.4b626b7b_at_posting.google.com>...
> 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 - 23:35:26 CEST