Re: Multi row updates on the primary key

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/24
Message-ID: <338946c8.5743558@newshost>#1/1

To update a table (T1) based on data in another (T2) you would:

IF many rows in T1 won't have a match in T2 and T2 is 'small'

update T1

set ( c1, c2, ... cN ) = ( select c1, c2, ... cN

                                from T2
                               where T1.pk1 = T2.pk1
                                 and T1.pk2 = T2.pk2 .... )
  where ( pk1, pk2, ... ) in ( select pk1, pk2, ....
                                 from T2 )

IF many rows in T1 WILL have a match in T2 and T2 is 'large'

update T1

set ( c1, c2, ... cN ) = ( select c1, c2, ... cN

                                from T2
                               where T1.pk1 = T2.pk1
                                 and T1.pk2 = T2.pk2 .... )

where exists ( select NULL from T2 where T1.pk1 = T2.pk1 .... ) /

On Fri, 23 May 1997 15:11:08 -0500, Programming <sysdev_at_mb.sympatico.ca> wrote:

>We are looking at updating a table.
>
>We have data in flat ascii files which will be
>inserted into the existing table with SQLLOAD.
>
>SQLLOAD does NOT update. Any duplicate
>records will be written to an dupes file.
>We can then import these into a temporary table,
>exactly the same as the first.
>
>The question is, without writing a 3GL type
>program,how can I:
>- update many records in the first table,
>with all the duplicate records in the second table?
>
>So far, I have only been able to:
>- update one row at a time, if looking at the key in the
>where clause.
>- update more than one row, but only if I am not looking at
>the primary key.
>
>So, in other words, is it possible to update all
>the records in the first table, while looking at
>the keys of both tables?
>
>
>Rodger

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 Sat May 24 1997 - 00:00:00 CDT