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

"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

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