Re: Can I do a join as part of an Update

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 30 Apr 2002 10:51:06 -0700
Message-ID: <aamlia01qsv@drn.newsguy.com>

In article <ucrdjub61k2ic0_at_corp.supernews.com>, "Sybrand says...
>
>
>"GB" <fatboyrider_at_hotmail.com> wrote in message
>news:600c339e.0204291110.1dc9a416_at_posting.google.com...
>> I have this in Sybase that I trying to convert to Oracle, is there a
>> way to convert this? I thought about subqueries but they only allow
>> one result where I will have many:
>>
>> update tmpTIS_IndvInfo
>> set PrimaryNumber = a.area_cde,
>> Ext = a.extn_num,
>> Mask = b.telecom_type_mask,
>> CountryCode = a.country_cde
>> from t_telecom a,
>> t_telecom_type b,
>> tmpTIS_IndvInfo c,
>> t_tele_phys d
>> where d.hr_primary_ind = 1 and
>> d.party_id_num = c.partyidnum and
>> c.partyidnum = a.party_id_num and
>> a.telecom_type_cde = b.telecom_type_cde and
>> d.cntc_mech_id_num = a.cntc_mech_id_num;
>
>No version of course.
>
>In 8i and higher you can update an inline view
>
>update
>(select <all columns needed, to be updated and to be used>
> from <all tables used>
>where <join conditions>
>)
>set
>

minor clarification -- Oracle 7 could do this as well:

ops$tkyte_at_ORA734.WORLD> create table t1 ( x int primary key, y int ); Table created.

ops$tkyte_at_ORA734.WORLD> create table t2 ( x int, y int ); Table created.

ops$tkyte_at_ORA734.WORLD> insert into t1 values ( 1, 1 ); 1 row created.

ops$tkyte_at_ORA734.WORLD> insert into t1 values ( 2, 2 ); 1 row created.

ops$tkyte_at_ORA734.WORLD> insert into t2 values ( 2, null ); 1 row created.

ops$tkyte_at_ORA734.WORLD> insert into t2 values ( 3, null ); 1 row created.

ops$tkyte_at_ORA734.WORLD> update ( select t1.y t1_y, t2.y t2_y

  2                     from t1, t2
  3                    where t1.x = t2.x )
  4     set t2_y = t1_y

  5 /

1 row updated.

ops$tkyte_at_ORA734.WORLD> select * from t2;

         X Y
---------- ----------

         2          2
         3

ops$tkyte_at_ORA734.WORLD>

It goes way back

>etc.
>
>You are, however capable to use a subquery for your purpose, namely a
>correlated subquery
>update <table to be updated>
>set <column to be updated>,... = -- multiple columns are possible
>(select ..
> from <tables>
> where <tables>.<primary keys> = <table to updated keys>)
>where
>exists
>(select 'x'
> from <tables>
> where <tables>.<primary keys> = <table to updated keys>)
>
>etc.
>
>Hth
>
>--
>Sybrand Bakker
>Senior Oracle DBA
>
>to reply remove '-verwijderdit' from my e-mail address
>
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Apr 30 2002 - 12:51:06 CDT