Re: How Do I do This Update

From: Gary Assa <gsa_at_panix.com>
Date: 1997/07/07
Message-ID: <5pqtv0$36d@panix.com>#1/1

>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