Re: Help me double check this query. Please?

If the matched columns are in the same row, then try

update table1 T1 set column_a = 1
where exists (select 'X' from table2
where rec_id = T1.rec_id and
rec_date = T1.rec_date)

Remco Blaakmeer wrote:

> 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 Wed Jan 12 2000 - 01:36:33 CST