How to create an all-or-nothing update query in Postgres?-CodePudding

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)

CodePudding user response：

Use transaction. Inside the transaction, execute the UPDATE. Check the number of rows updated. If that number is less than the length of the list of IDs, abort the transaction with ROLLBACK, else COMMIT.

CodePudding user response：

Yet another option is to check when the couple of <user_id, item_id> is not present with a quantity equal to 0, using the NOT EXISTS operator.

UPDATE items i
SET quantity = quantity - 1 
WHERE user_id = $1 
  AND item_id IN (5, 6, 7)
  AND NOT EXISTS (SELECT user_id, item_id 
                  FROM items 
                  WHERE i.user_id = user_id AND i.item_id = item_id 
                    AND quantity = 0);

Check the demo here.