Re: update, select and join in one statement ?

A copy of this was sent to ghubert_at_netpath.net (Gene Hubert) (if that email address didn't require changing) On Tue, 31 Aug 1999 03:23:52 GMT, you wrote:

>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

from the 7.3 manual I deduced it quite easily actually. The wire diagram for update is:

>>--UPDATE----------------------------------------------------->
>-------+---------+--+-Table--------+---+---------+--+--------+-->

        |         |  +-View---------+   |         |  |        |
        +-schema.-+  +-snapshot-----+   +-_at_dblink-+  +-talias-+
                     +-(subquery_1)-+

.....

and is then followed by:

subquery_1: is a subquery that Oracle treats in the same

               manner as a view. For the syntax of subquery, 
               see page 4 – 431.

It also shows and explains the usage of subqueries in 2 other ways in the set clause of the update as well. In this particular case -- I myself feel the documentation hasn't let us down. The same is true for INSERT and DELETE statements -- as well as SELECTs. They all can use a subquery where a TABLE is normally found.

>suppose I should just be delighted to learn another Oracle trick and
>leave it at that.
>

No, the stuff in Oracle changes lots from release to release. This wasn't an 'insiders' trick -- its pretty much straight from the manuals. I try to read the server concepts from cover to cover with every release (excellent manual, understand that and you understand the database) and at least skim the wire diagrams for sql syntax to pick up new features (eg: thats how I found "alter table X move tablespace Y" in Oracle8i. There was no glaring section or chapter on it -- its just mentioned in the alter table command. Lets me move a table from one tablespace to another without using exp/imp and rebuilding everything. One of my favorite new 8i features but it doesn't get alot of press)

>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 Tue Aug 31 1999 - 13:06:57 CDT