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

In Oracle (as opposed to some other databases...) you can use subqueries that return more than one column in the select list (assuming the subquery returns no more than 1 record for each record in the updated table):

update tmpTIS_IndvInfo c
set (PrimaryNumber,Ext,Mask,CountryCode) = (select a.area_cde, a.extn_num, b.telecom_type_mask, a.country_cde
from t_telecom a,
t_telecom_type b,
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);

Oren.

"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;
Received on Mon Apr 29 2002 - 16:05:10 CDT