如何在Postgres中创建全有或全无的更新查询?

Question

我有以下查询:

UPDATE items SET quantity = quantity - 1 
WHERE quantity > 0 AND user_id = $1 AND item_id IN (5, 6, 7);

我想修改它，使更新只会在所有三行都更新时发生。

也就是说，除非用户的项5、6、7的数量都大于0，否则将更新0行。但是，如果每个条件都为真，则更新所有三行。

我不确定有什么简单的方法来做这件事。我的直觉解决方案是使用CTE，其中初始查询获得COUNT，然后仅在COUNT = 3时执行更新，但我认为必须有更好的方法?

同样，我在这里用3个项目作为例子。item_id的数量是可变的，在我的情况下可以在1到20之间的任何地方(从应用服务器作为数组传递)

Answer 1

使用事务。在事务内部，执行UPDATE。检查已更新的行数。如果该数字小于id列表的长度，则终止与ROLLBACK的事务，否则为COMMIT。

Answer 2

另一个选项是检查<user_id,>使用NOT EXISTS运算符，不存在等于0的数量。

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

点击这里查看演示。

Answer 3

为表quantity >= 0添加了一个检查约束，然后执行如下操作:

UPDATE items SET quantity = quantity - 1 
WHERE user_id = $1 AND item_id IN (5, 6, 7);