Re: Join Update Query?!!!

From: Doogie <jvivona_at_mindspring.com>
Date: 1997/11/03
Message-ID: <345E9C3B.5472@mindspring.com>#1/1

> 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';
>

How about

    UPDATE TABLE_A A
    SET FIELD_ID = '0001AA'
    where A.FIELD_ID = '0001'

    AND     exists (   select   b.field_b
                       from     table_b b
                       where    b.field_b = a.field_a)

;

This works really well if field_a and field_id are a compound index on table_a and field_b is a simple index on table_b.....

Hope this helps...

Joe V Received on Mon Nov 03 1997 - 00:00:00 CST