Re: How Do I do This Update

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/06/30
Message-ID: <33b9cebb.6879101@newshost>#1/1

On 30 Jun 1997 13:25:24 GMT, Brett Moran <bmoran_at_isus.msstate.edu> wrote:

>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
>B. Can someone help. Please email. Thanks.

update A

   set ( field1, field2, ... fieldN ) =

       ( select fieldA, fieldB, ..., fieldN )
           from B
          where B.partno = A.partno )

where

   (partno1,partno2,..,partnoN) in ( select B.partno1, B.partno2,... from B )

Or

update A

   set ( field1, field2, ... fieldN ) =

       ( select fieldA, fieldB, ..., fieldN )
           from B
          where B.partno = A.partno )

where

   exists ( select NULL from b where b.partno1 = a.partno1 AND .... )

will do it.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 30 1997 - 00:00:00 CDT