Re: What is wrong with my SQL ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 26 Mar 1999 12:42:05 GMT
Message-ID: <3702803c.6600801@192.86.155.100>

A copy of this was sent to "Jørgen Haukland" <jorgen.hauikland_at_fou.telenor.no> (if that email address didn't require changing) On Fri, 26 Mar 1999 13:10:42 +0100, you wrote:

>Hello !
>
>I'm trying to update a column in my main table with values from a similar
>column in another table when the primary keys column match. This is my SQL:
>
> UPDATE TableA
> SET TableA.Col1 = TableB.Col1
> WHERE TableA.PrimaryKeyCol = TableB.PrimaryKeyCol
>
>I get an "invalid column name" error whenn I try this.
>

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.

Alternatively, another way is:

update tablea

   set old_field = ( select new_field

                       from tableb 
                      where tablea.my_id = tableb.my_id )
 where exists ( select NULL 
                  from tableb
                 where tablea.my_id = tableb.my_id )

or

update tablea

   set old_field = ( select new_field

                       from tableb 
                      where tablea.my_id = tableb.my_id )
 where my_id in ( select my_id
                    from tableb )

>What is wrong ?
>
>Joergen Haukland
>NORWAY
>
 

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 Fri Mar 26 1999 - 06:42:05 CST