Update table with multiple joins

[drahmani]

Can anyone tell me how to do this in Oracle?
This is not the exact query but I wanted to simplify it a little. The problem I see is that the WHERE clause depends on the join which I cannot do in Oracle.
------ A
update table1 t1
set t1.pdesc=t2.pdesc, t1.pcost=t3.pcost
from table1 t1 left outer join table2 t2 on t1.name=t2.name
left outer join table3 t3 on t2.product=t3.product
where t1.site='MD' and t1.pdesc is null and t2.pdesc is not null and t1.pcost is null and t3.pcost is not null
------ A
When I try this code below, I get :
ORA-01427: single-row subquery returns more than one row.
------ B
update table1 t1
set (t1.pdesc, t1.pcost)=
(select t2.pdesc, t3.pcost
from table1 t1 left outer join table2 t2 on t1.name=t2.name
left outer join table3 t3 on t2.product=t3.product
where t1.site='MD' and t1.pdesc is null and t2.pdesc is not null
and t1.pcost is null and t3.pcost is not null)
------ B

There will be multiple rows of the same name and product combination but each name and product will only have one pdesc and pcost value.

Thank you!
drahmani

[QVeen72]

Hi,

Break the Query into 2, Update seperately for PDesc and Cost:

update table1 tMain Set (Pdesc) =
(Select t2.pdesc
from table1 t1 left outer join table2 t2 on t1.name=t2.name
where t2.pdesc is not null And T1.Site='MD' And T1.Name = TMain.Name)
Where Site ='MD' And PDesc Is Null


update table1 tMain Set (PCost) =
(Select t3.pcost
from table2 t2 left outer join table3 t3 on t2.product=t3.product
where t3.pcost is not null And T2.Name = TMain.Name)
Where Site ='MD' And PCost Is Null

Regards
Veena