Re: update, select and join in one statement ?

On Mon, 30 Aug 1999 16:33:58 GMT, tkyte_at_us.oracle.com (Thomas Kyte) wrote:

Well, that's a new one on me. Very nifty trick there Thomas. Much cleaner than adding an exists clause.

I'm really glad to pick up a handy technique like this but I have to ask: How is anybody supposed to deduce something really useful like this from the documentation provided for the update statement? I suppose I should just be delighted to learn another Oracle trick and leave it at that.

Gene Hubert
SkillsPoint.com

>
>
>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 - 22:23:52 CDT