Re: Help me double check this query. Please?

Try this :
....
where (T1.rec_id,t1.rec_date) in
 (select rec_id,rec_date from table2 t2

        where t1.rec_id = t2.rec_id and t1.rec_Date = t2.rec_Date)

kmidkiff <kmidkiff_at_home.com> a écrit dans le message : 387C2EED.1177F8C4_at_home.com...
> 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 - 02:57:57 CST