Re: Join Update Query?!!!

Doogie wrote:
>
> > 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

Field_a does not need to be included in the index on table_a, since it does not form part of the access path. You are (correctly) accessing table_a by field_id alone and table_b by field_b alone.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"