new to Pl/SQl developer

[DKP]

Hi All,

How can i update dh.amountdelivered with pl.deliveredamount
the select is working but when i am trying to Update then (single row subquery returns more than one Row)

Update deliveryhistory dk
set dk.amountdelivered = (select pl.deliveredamount
from packingnoteline pl, packingnote p, deliveryhistory dh
where dh.product_link_id = pl.product_id
and dh.sizedefinition_link_id = pl.sizedefinition_id
and pl.invoiceheader_id is null
and pl.tobedeliveredamount = 0
and pl.systemuser_id not in(1124, 344,345,1183)
and pl.creationsystemuser_id = 786
and pl.finishingmethod_id = dh.finishingmethod_id
and dh.deliverydate = p.deliverydate
and dh.distributionpoint_link_id = p.distributionpoint_id
and p.packingnote_id = 1699328
and p.status_id = 206
and p.packingnote_id = pl.packingnote_id
and dh.finishingmethod_id = 202
and dh.amountdelivered pl.deliveredamount
and dh.distributionpoint_link_id = 22893)

 

[mike]

This is not really a PL/SQL Developer question, but a SQL question. It would be more appropriate to ask a co-worker or friend or on a SQL forum.

Your subquery returns more than one row. The way you have it written is that you want every dk.amountdelivered to be updated with pl.deliveredamount. The problem is that there is more than one pl.deliveredamount and you can only put one in per record.

I don't know your table schema, so I won't be able to re-write your query for you. That's why a co-worker would be better to ask. To fix it though, you need to somehow have each dk.amountdelivered be updated with one pl.deliveredamount. You need to have it as UPDATE/SET/WHERE. Have the WHERE part be most of your subquery and reference which dk record is being updated with which pl.delivered_amount.