SQL 更新 : return if row was updated AND exists

Question

我为我的 API 编写了一个查询，如果资源不处于请求的有效状态，我必须返回 冲突，或者如果资源不存在，则返回未找到存在。

我不想向数据库发送 2 个查询(首先检查资源是否存在，然后更新它)。因为仅仅检查行是否被更新是不够的，可能资源不存在。

我总是希望返回一行看起来像这样:

 exists | updated
--------+---------
 t      | t
(1 row)

我的查询有效，我只是想知道是否有更简单的方法...

WITH updates AS (
    UPDATE abc
    SET status = 'cancelled'
    WHERE id = $1 AND status = 'active' RETURNING 1
)
SELECT
    EXISTS(SELECT 1 FROM abc WHERE id = $1) as exists,
    EXISTS(SELECT 1 FROM updates) as updated

Answer 1

我突然想到，如果只有 RETURNING 可以在 UPDATE 之前提供原始的 status，您就可以这样做。这是不可能的，但是another question指出一个技巧:连接到同一个表的另一个副本，并返回其他表的列!

所以这似乎适用于您的情况:

UPDATE  t
SET     status = (CASE WHEN t.status = 'active' THEN 'canceled' ELSE t.status END)
FROM    t AS t2
WHERE   t.id = t2.id
AND     t.id = 1
RETURNING t.id, t.status, t2.status
;

当然，您可以调整 RETURNING 以提供更具描述性的内容，例如CASE WHEN t2.status = 'active' THEN 'updated' ELSE 'invalid' END)，但我在这里展示了如何返回所有“有趣”的值以演示可能的情况。所以:如果缺少 id，您将得不到任何结果。如果 id 存在但已经非 active，则两个 status 列匹配。如果已更新，则 status 列不同。