Re: Oracle Update SQL Help

Lanky wrote in message <8dj37n$7rrch$1_at_fu-berlin.de>...
>Didn't work got a Oracle error 1773 can't update a rs
>
>--
>Thanks,
>
>Lanky
>"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.
>
>

Try this:

tablea:

     ID col2

  Col1         JKL
  Col2        NULL
  Col3        NULL
  Col4        NULL
  Col5        ABC

tableb:
     ID            col2                1
-------------------------
  Col1        JKL
  Col2        ABC
  Col3        ABC
  Col4        ABC
  Col5        ABC

update tablea
set col2=( select col2

                  from tableb
                  where tablea.ID=ID)
 where col2<> ( select col2
                            from tableb
                            where tablea.ID=ID)