Re: Update one table by another

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Mar 1999 18:50:04 GMT
Message-ID: <370433bb.23877133@192.86.155.100>

A copy of this was sent to Surfer Netzbetrieb <ep_nm_ka_at_yahoo.de> (if that email address didn't require changing) On 24 Mar 1999 18:22:01 GMT, you wrote:

>Hello,
>
>how can I update a colum in one table by another table's column in SQL
>(same key)?
>
>primary-key: my_ID
>
>
> TableA TableB
> ===== ======
> my_ID my_ID
> old_field <<= new_field
>UPDATE !
>
>
>My first tries are very complex:
>
> update TableA
> set old_field =
> (Select new_field from TableB
> where my_ID in
> (Select my_ID from TableA aliasTabA where
>aliasTabA.my_ID = TableA.my_ID))
>
>
>Can somebody correct/ adjust/ improve my update ?
>
>Thank you
>Ekkard Gerlach
>

Here is one way to do it:

SQL> create table tablea ( my_id int primary key, old_field varchar2(25) ); Table created.

SQL> create table tableb ( my_id int primary key, new_field varchar2(25) ); Table created.

SQL> insert into tablea values ( 1, 'Old Data' );
SQL> insert into tablea values ( 2, 'Old Data' );
SQL> 
SQL> insert into tableb values ( 1, 'New Data' );
SQL> insert into tableb values ( 3, 'New Data' );

SQL> select * from tablea;

     MY_ID OLD_FIELD

---------- -------------------------
         1 Old Data
         2 Old Data

SQL>
SQL> update
  2 ( select tablea.my_id, tablea.old_field, tableb.new_field

  3          from tablea, tableb
  4     where tablea.my_id = tableb.my_id )
  5     set old_field = new_field

  6 /

1 row updated.

SQL>
SQL> select * from tablea;

     MY_ID OLD_FIELD

---------- -------------------------
         1 New Data
         2 Old Data

Update the join.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 24 1999 - 12:50:04 CST