Re: SQL Question: referencing two tables in update statement

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/30
Message-ID: <340b7c6a.23101057_at_newshost>#1/1

On Thu, 28 Aug 1997 10:16:02 -0700, schaulk_at_hivnet.ubc.ca (Stuart Chaulk) wrote:

>Hi there,
>
>Quick SQL guestion:
>
>How do I reference two tables in an SQL statement. What I need is
>something like this, but not sure about the syntax:
>
>update table_1 ref1, table_2, ref2
>set ref1.field_1 = ref2.field_1
>where ref1.field_2 = ref2.field2;
>
>Any help would be VERY MUCH appreciated.
>
>Thanks,
>
>Stuart

the general syntax is:

update TABLE_1

set ( a, b, c, ... ) = ( select table_2.a, table_2.b, table_2.c, ....
                           from Table_2
                          where table_1.field1 = table_2.field1, 
                            and table_1.field2 = table_2.field2, 
                            ...... )

/

If there exists rows in TABLE_1 that have no match in TABLE_2 you must modify the above like:

update TABLE_1

set ( a, b, c, ... ) = ( select table_2.a, table_2.b, table_2.c, ....
                           from Table_2
                          where table_1.field1 = table_2.field1, 
                            and table_1.field2 = table_2.field2, 
                            ...... )

where ( field1, field2, ... ) in ( select field1, field2 .... from table_2 ) /

or

update TABLE_1

set ( a, b, c, ... ) = ( select table_2.a, table_2.b, table_2.c, ....
                           from Table_2
                          where table_1.field1 = table_2.field1, 
                            and table_1.field2 = table_2.field2, 
                            ...... )
where exists ( select table_2.a, table_2.b, table_2.c, ....
                           from Table_2
                          where table_1.field1 = table_2.field1, 
                            and table_1.field2 = table_2.field2, 
                            ...... )

/

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 Sat Aug 30 1997 - 00:00:00 CEST