Re: Join Update Query?!!!

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 CET