Re: Oracle SQL: UPDATE using Subquery or Join
Date: 26 May 2003 22:51:37 -0700
Message-ID: <51a9e702.0305262151.364d7b7e_at_posting.google.com>
Hallo John,
you can take this - not often seen, but it works ;-)
update (select a.name a_name,b.name b_name
hth
Ralf
from a,b
where a.id=b.id)
set a_name=b_name
---
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 - 07:51:37 CEST