Re: How Do I do This Update

I aggree, but I must correct one thing. For performance, you should use EXISTS instead of IN:

 update tablea T set col1=(select col1 from tableb B

                           where B.part_num=T.part_num)
 where EXISTS (select 1 from tableb B
                         where B.part_num = T.part_num);
  

Gary Assa <gsa_at_panix.com> wrote in article <5pqtv0$36d_at_panix.com>...
> >I have table A with 5000+ rows and table B with 2200+ rows. They are
> >keyed on a part number. Table A includes all part numbers, table B only
> >those that need to be updated. I've tried using an update with an
> >embedded select using several combinations and approaches. They all
> >update every row of table A instead of the rows that have a match in
 table
>
> A common problem, but here's the solution:
>
> update tablea T set col1=(select col1 from tableb
> where part_num=T.part_num)
> where part_num in (select part_num from tableb);
>
> That last part is the trick. This way, it only updates rows that it finds
 

> Without it, it tries to match all rows, and when there is no match, it
 will
> update it with a null.
>
>
Received on Mon Jul 07 1997 - 00:00:00 CDT