I have the following query:
UPDATE items SET quantity = quantity - 1
WHERE quantity > 0 AND user_id = $1 AND item_id IN (5, 6, 7);
I'd like to modify it such that the update will only occur if all three rows are updated.
That is, unless that user has items 5, 6, 7 with quantities greater than 0 for each of them, 0 rows will be updated. However, if the condition is true for each, then all three rows are updated.
I'm not sure of a simple way to do this. My gut solution is to use a CTE where the initial query gets the COUNT and then you only perform the update if the count = 3, but I think there must be a better way?
Also, I am using 3 items here as an example. The number of item_ids is variable, and can be anywhere between 1 and 20 in my case (passed from the app server as an array)
Added a check constraint to the table quantity >= 0 and then just did this:
UPDATE items SET quantity = quantity - 1
WHERE user_id = $1 AND item_id IN (5, 6, 7);