Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: update, select and join in one statement ?
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