Re: Oracle Update SQL Help

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/19
Message-ID: <8divd6$uk4$1@nnrp1.deja.com>#1/1

In article <8dis0m$7p9e5$1_at_fu-berlin.de>,   "Lanky" <mdang_at_NOSPAMmy-deja.com> wrote:
> Hi All,
>
> Here's the scenario.
>
> 2 Tables Exactly the same data structure and data except for 3 fields
 in
> table A are null.
>
> 1 primary key in both tables. The data is all the same in Table A & B
> except for 3 fields in Table A.
>
> Data Structure for both TBL A & B (example 1 row of data given below)
> TBL A TBL B
> PK - ID 1 1
> Col1 JKL JKL
> Col2 NULL ABC
> Col3 NULL ABC
> Col4 NULL ABC
> Col5 ABC ABC
>
> The data is the same just need to get the 3 fields in Table B into
 TAble A.
>
> This is what I got but didn't work.
>
> Update Table B
> set TableB.col1 = TableA.col1,
> TableB.col2 = TableA.col2,
> TableB.col3 = TableA.col3
> from table A
> where TableA.id = TableB.id
>

you can update a join this way:

update
( select tableb.col1 b_col1, tableb.col2 b_col2, tableb.col3 b_col3,

         tablea.col1 a_col1, tablea.col2 a_col2, tablea.col3 a_col3     from tableA, tableB
   where tableA.id = tableB.id )

set b_col1 = a_col1,
    b_col2 = a_col2,
    b_col3 = a_col3

/

A correlated subquery would be another way:

update tableB
  set (col1, col2, col3 ) = ( select col1, col2, col3

                                 from tableA
                                where tableA.id = tableB.id )
 where exists ( select col1, col2, col3
                                 from tableA
                                where tableA.id = tableB.id )

/

> --
> Thanks,
>
> Lanky
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Apr 19 2000 - 00:00:00 CDT