Re: UPDATE QUERY
Date: 1996/09/13
Message-ID: <3238b87d.2464944_at_dcsun4>#1/1
On Thu, 12 Sep 1996 21:43:49 GMT, dgsmith_at_wb.xerox.com wrote:
>
>I am trying to write an UPDATE query. I have 2 tables.
>Both have a part # and cost field. I want to match
>the tables on part # and update the price on one table
>with the price in the other table.
>
>Any help would be appreciated.
>
>Regards,
>
>Dave Smith
>
The general format will be:
update T1
set ( c1, c2, c3, ... ) =
( select k1, k2, k3, ...
from T2
where T2.x = T1.x
and .... )
where exists
( select NULL
from T2
where T2.x = T1.x
and .... )
/
Which will full scan T1 and update any row with a mate in T2. Use if most rows in T1 will have mates in T2 anyway.
update T1
set ( c1, c2, c3, ... ) =
( select k1, k2, k3, ...
from T2
where T2.x = T1.x
and .... )
where ( x, y, z ) in
( select x, y, z
from T2 )
/
Which will index range scan T1 (if index on x,y,z). Use if only a couple of rows in T1 will get updated.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Sep 13 1996 - 00:00:00 CEST