| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Updateable joins in Oracle via ODBC? How?
A copy of this was sent to "Randy Baker" <rsbakerZ_at_msn.com> (if that email address didn't require changing) On Wed, 25 Feb 1998 15:20:36 -0500, you wrote:
>MS Access and, to a less extent, SQL Server allow UPDATE statements to
>contain joins. Does Oracle do this, and if so, could someone help me with
>the syntax. If not, what alternative approaches are recommended?
Instead of updating a JOIN, you use a correlated update as such:
update T1
set ( a, b, c ) = ( select d, e, f
from T2
where T2.pk = T1.pk )
So, this will for each row in T1 find the matching row in T2 by primary key and update a, b, c in T1 to be whatever values are in d, e, f. If you want to only update the rows in T1 that have a mate in T2, you would code:
update T1
set ( a, b, c ) = ( select d, e, f
from T2
where T2.pk = T1.pk )
where (t1.pk) in ( select t2.pk from t2 )
or
update T1
set ( a, b, c ) = ( select d, e, f
from T2
where T2.pk = T1.pk )
where exists ( select NULL from t2.pk = t1.pk )
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Feb 26 1998 - 00:00:00 CST
 |
 |