Re: *PLEASE* Help with an UPDATE query...Thanks

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/19
Message-ID: <3422d514.99835005@newshost>#1/1

On 19 Sep 1997 17:25:19 GMT, Nnoor_at_cris.com (NNOOR) wrote:

>
>Hi,
>I have two tables--call them A and B.
>I want to update certain fields in A from values in table B based
>on a selection criteria.
>
>The "select" portion would be like:
>SELECT * from A
>WHERE A.ID = B.ID AND A.ID in ('<a list of values>');
>
>And then based on above select, I want to update table A:
>UPDATE A
>SET A.someField = B.someField;
>
>I can't seem to figure out how to combine the two in one query (or
>nested sub-queries). Is it possible at all? If not, what would be the
>best way, in your opinion, to do this?
>
>Thanks very much for your help!!!
>
>Regards,
>Nasir (NNoor_at_cris.com)

update A
set ( a.c1, a.c2, ... ) = ( select b.c1, b.c2, ....

                              from b
                             where b.id = a.id )

where a.id in ( 'a list of values' )
/

And if 'a list of values' might contain values NOT in B then you need

where a.id in ( select b.id from b where b.id in ( 'a list of values' ) )

because if the query ( select b.c1, b.c2, ... from b where b.id = a.id ) returns 0 (or more then 1) row, the update will fail.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 19 1997 - 00:00:00 CDT