Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Join Update Query?!!!

Re: Join Update Query?!!!

From: Peter Schneider <pschneider.ctj_at_metronet.de>
Date: 1997/11/04
Message-ID: <345fa2c2.3046467@pop-news.metronet.de>#1/1

On 3 Nov 1997 23:46:49 GMT, "James Wj Snyder" <snyderj_at_wellsfargo.com> wrote:

>Hello peoples,
>
>Again Oracle support has been no help <sigh> Anywho...
>
>I need to do an update query based on crieteria from a join. Any help
>would be appreicated.
>
>Two tables:
>TABLE_A
>TABLE_B
>
>Update TABLE_A set FIELD_ID = '0001AA' where
>TABLE_A.FIELD_A = TABLE_B.FIELD_B and where
>TABLE_A.FIELD_ID = '0001';
>
>
>Anything?
>
>--
>Peace,
>James Wj Snyder
>Wells Fargo Bank

Hi James,

as you can transform a join (which is not supported in update statements) to a correlated subquery (which is supported), and given that I understood correctly what you were trying to do, the following statement should do the trick:

UPDATE table_a a
SET a.field_id = '0001AA'
WHERE a.field_id = '0001'
AND EXISTS
(SELECT 'TRUE'
FROM table_b b
WHERE a.field_a = b.field_b);

Hope this helps,
Peter

Peter Schneider
pschneider.ctj_at_metronet.de Received on Tue Nov 04 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US