UPDATE QITEM
SET QTYONHAND = QTYONHAND + (SELECT SUM(DELQTY)
FROM QDEL
WHERE QITEM.ITEMNAME = QDEL.ITEMNAME);
I am trying to update an inventory column where the intial values are all 1000 and I am adding the number of deliveries from the QDEL table to that 1000. This works but there are null values present in the rows with no deliveries in the QDEL table. I cannot figure out why???
Because SUM returns NULL if all DELQTY rows are NULL. You can do something like this :
UPDATE QITEM
SET QTYONHAND = QTYONHAND + COALESCE((SELECT SUM(DELQTY)
FROM QDEL
WHERE QITEM.ITEMNAME = QDEL.ITEMNAME), 0);
You need to add WHERE EXISTS so you're only updating rows where there is at least one corresponding record in QDEL:
UPDATE qitem qi
SET qi.qtyonhand = qi.qtyonhand + ( SELECT SUM(qd.delqty)
FROM qdel qd
WHERE qd.itemname = qi.itemname )
WHERE EXISTS ( SELECT 1 FROM qdel qd
WHERE qd.itemname = qi.itemname );
P.S. If it's possible for the value of delqty in QDEL to be NULL, you'll want to use SUM(NVL(qd.delqty, 0)) (or SUM(COALESCE(qd.delqty, 0))) rather than SUM(qd.delqty).
Use NVL to always return a sum, even if no deliveries have taken place.
UPDATE QITEM
SET QTYONHAND = QTYONHAND + (SELECT NVL(SUM(DELQTY),0)
FROM QDEL
WHERE QITEM.ITEMNAME = QDEL.ITEMNAME);
