Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: update, select and join in one statement ?

Re: update, select and join in one statement ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 30 Aug 1999 16:33:58 GMT
Message-ID: <37d0b258.14770518@newshost.us.oracle.com>


A copy of this was sent to "Michael Keppler" <Michael.Keppler_at_bigfoot.com> (if that email address didn't require changing) On Sat, 28 Aug 1999 21:49:28 +0200, you wrote:

>Hello everybody !
>
>I try to build a statement like this:
>
>update table1
>set table1.columnA=table2.columnA
>where table1.columnB=table2.columnB
>
>Is something like this possible? I just don't get it right.
>What I want is to "copy" the information of table2.columnA into
>table1.columnA, but only if there is a join on the columns B possible
>for the row that is to be updated.

as long as table2.columnB is unique (a primary key or unique) this will do it:

tkyte_at_8.0> create table table1 ( columna int, columnb int ); Table created.

tkyte_at_8.0> create table table2 ( columna int, columnb int unique ); Table created.

tkyte_at_8.0> insert into table1 values ( null, 1 ); 1 row created.

tkyte_at_8.0> insert into table2 values ( 99, 1 ); 1 row created.

tkyte_at_8.0> select * from table1;

   COLUMNA COLUMNB
---------- ----------

                    1

tkyte_at_8.0> update
  2 ( select table1.columna t1a, table2.columna t2a   3 from table1, table2
  4 where table1.columnb = table2.columnb )   5 set t1a = t2a
  6 /

1 row updated.

tkyte_at_8.0>
tkyte_at_8.0> select * from table1;

   COLUMNA COLUMNB
---------- ----------

        99 1

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 30 1999 - 11:33:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US