Re: Oracle SQL: UPDATE using Subquery or Join

From: Dimitri Ang <dimitri_ang_at_allmail.net>
Date: 26 May 2003 18:15:21 -0700
Message-ID: <e18470c.0305261715.62a1bc6d_at_posting.google.com>


There should be something better but you can try this:

update a set name=(select name from b)
where exists (select id from b where id=a.id);

It will do what you want.

Regards,
Dimitri

thecake_at_rochester.rr.com (John Marchioli) wrote in message news:<90e6c07.0305260338.4871d1ca_at_posting.google.com>...
> I have a question about how to accomplish a table update using values
> stored in another table? SQL Server allows this to be accomplished
> using the FROM clause on an UPDATE statement. I noticed this cannot
> be done in Oracle and assume I have to use some subquery or something.
> I have an example of data and what I am trying to accomplish below.
> Can someone please let me know if this can be done with a single
> statement or am I executing a thousand UPDATE calls?
>
> Problem: Need to update A.NAME with B.NAME WHERE A.ID=B.ID
>
> TABLE A
> ID NAME
> 1 OldName1
> 2 OldName2
> 3 OldName3
> 4 OldName4
>
> TABLE B
> ID NAME
> 2 NewName2
> 4 NewName4
> 6 NewName6
>
> Notice table B does not have all the IDs of A!!!
>
> The is what I would like to see after executing a single UPDATE
> statement:
> TABLE A
> ID NAME
> 1 OldName1
> 2 NewName2
> 3 OldName3
> 4 NewName4
>
> Any help would be greatly appreciated. Thanks.
>
> John
Received on Tue May 27 2003 - 03:15:21 CEST

Original text of this message