CODEWITHSUNDEEP
mysql
Chris Ha
Chris Ha

Reputation: 311

UPDATE old value using a conditional SET statement

I have an inventory table which contains all kinds of items like, lets say, 'milk', 'apple', 'tea'. These items have a quantity value. My second table is the table orderedItem. This table contains certain orders like 'apple'x2, 'apple'x3, 'milk'x5.

My query has to pick certain order rows (in my example its an order featuring a certain amount of 'apples') from the orderedItem table, take the quantity of that order, and add/substract that quantity to/from the according row/value in the inventory table.

How can I fix this SQL query - I think the code written inside the bracelets 'quantity FROM orderedItem WHERE productname='apple'' might be broken:

query = mysql_query ("
    UPDATE inventory iv
    SET quantity = quantity - (quantity FROM orderedItem WHERE productname='apple')
    WHERE iv.productname = 'apple' ");

Upvotes: 1

Views: 192

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

You can nest the select:

UPDATE inventory iv
    SET quantity = quantity - (select quantity FROM orderedItem WHERE productname ='apple')
    WHERE iv.productname = 'apple' ;

This will not work if more than one row matches the condition (or even if none do). So, you probably want:

UPDATE inventory iv
    SET quantity = quantity - coalesce((select sum(oi.quantity)
                                        from orderedItem oi
                                        where productname = oi.productname
                                       ), 0)
    WHERE iv.productname = 'apple' ;

Upvotes: 1

Related Questions