Re: Oracle Update SQL Help

[Quoted] [Quoted] Didn't work got a Oracle error 1773 can't update a rs

--
Thanks,

Lanky
[Quoted] "Thomas J. Kyte" <tkyte_at_us.oracle.com> wrote in message
news:8divd6$uk4$1_at_nnrp1.deja.com...


> 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.