kincaid kincaid - 7 months ago 9
SQL Question

Update value based on subquery returning more than one row

I have this probably simple SQL question.

UPDATE parts
SET partscnt=partscnt-1
WHERE idparts = (SELECT idparts FROM ordercontent WHERE idOrder=4);


Problem is that the subquery returns 5 values, and i want the update done on each of the values.

Update:
The ordercontent table has an ID for the order and a row idparts. If several of the same parts are hooked to the same order the table looks like this:

ID idparts
4 147
4 147
4 147
4 55
4 33


So I need to decrement the partscount three times for the part with id 147.

How can I change this query to achieve that?

Answer

Find the quantity of every idparts in ordercontent for the specified order:

SELECT idparts,
       COUNT(*) AS cnt
FROM ordercontent
WHERE idOrder = 4
GROUP BY idparts

Join the result of the above to parts and use the cnt values to update partscnt:

UPDATE parts p
INNER JOIN (
    SELECT idparts,
           COUNT(*) AS cnt
    FROM ordercontent
    WHERE idOrder = 4
    GROUP BY idparts
) o
ON p.idparts = o.idparts
SET p.partscnt = p.partscnt - o.cnt
;