CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Zac Davidson
Zac Davidson

Reputation: 247

ORACLE SQL Developer Update Statement

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???

Upvotes: 0

Views: 2770

Answers (3)

David Faber
David Faber

Reputation: 12485

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).

Upvotes: 2

RudolphEst
RudolphEst

Reputation: 1250

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);

Upvotes: 1

potashin
potashin

Reputation: 44581

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);

Upvotes: 2

Related Questions