Re: Help me double check this query. Please?

In article <09920fb9.06801268_at_usw-ex0102-013.remarq.com>,

        Greg Akins <gakinsNOgaSPAM_at_gatewayhealthplan.com.invalid> writes:
> Hi,
>
> I have a query which is updating one field in a record, if two fields
> in that record match 2 fields in a record of another table.
>
> Basically:
>
> update table1 t1 set column_a = 1
> where T1.rec_id in (select rec_id from table2 t2
> where t1.rec_id = t2.rec_id )
> and t1.rec_date in (select rec_date from table2 t2
> where t1.rec_Date = t2.rec_Date)
>
> Does this do what I expect it to? Is there a cleaner way to accomplish
> this?

No, this doesn't do what you expect it to do. This matches a record in table1 if a record exists in table2 with the same rec_id as the record in table1 and a record exists in table2 with the same rec_dat as the record in table1. The record in table2 that is found in the first subquery doesn't need to be the same record as the one that is found in the second subquery.

Use something like

update table1 t1 set column_a = 1
where (t1.rec_id, t1.rec_date) in (select rec_id, rec_date

                                   from table2 t2
                                   where t2.rec_id = t1.rec_id
                                   and t2.rec_date = t2.rec_date)

or use EXISTS, like somebody else suggested. I think using EXISTS is faster in this case, as EXISTS never fetches more than one row from a subquery.

Remco
--
rd1936: 12:00am up 2 days, 4:39, 10 users, load average: 2.40, 1.99, 2.17 Received on Mon Jan 10 2000 - 17:10:05 CST