Update table with multiple columns from more than one table

[chrisz]

Hi,

I wish to update one column in a table
, with one column from TWO MULTI-ROW tables.

I can't do it without setting a rownum of 1, to force the update to select only row one from each table. Whereas I want ALL rows from both tables added together and stored onto the one row in the destination table.

Been stuck on this for WEEKS! LOL
You're help is REALLY appreciated.

e.g.

Destination table: ROUTESTOP

Source tables: DEPARTMENT and DISTRIBUTIONPOINT

The ROUTESTOP table can link to more than one DEPARTMENT row, linked on the customer_id key.
The DEPARTMENT table can linke to more than one DISTRIBUTIONPOINT row, linked on the department_id key.

see below:

update routestop rs

set rs.routelistinstructions =

concatenate3(rs.routelistinstructions,
chr(10),
(concatenate3('#### Delivery Point: ',
(select dep.description
from department dep
where rs.customer_id = dep.customer_id
and rownum = 1), -- I can only load one row from table: department
concatenate3(', Barcode Location: ',
(select dp.driverinstruction
from distributionpoint dp,
department dep
where dp.department_id =
dep.department_id
and dep.customer_id =
rs.customer_id
and rownum = 1), -- I can only load one row from table: distributionpoint
concat(' ####', chr(10))))))

where exists (select 1
from customer c, department dep, distributionpoint dp
where RS.CUSTOMER_ID = C.CUSTOMER_ID
AND C.CUSTOMER_ID = DEP.CUSTOMER_ID(+)
AND DEP.DEPARTMENT_ID = DP.DEPARTMENT_ID(+)
and dp.proofofvisitbarcode is not null
and dp.driverinstruction is not null

AND C.CUSTOMERNUMBER = '3490') -- only using one customer as a test

 

[null0000]

and how is your question related to the "PL/SQL developer" as a product???

 

[chrisz]

Good point. Ignore it then.

Thanks for your advice.

I'll look elsewhere.