sql - Postgresql 从上次更新语句中获取行列表

Question

如何获取使用上一个 UPDATE 语句更新的行的列表？

例如:

UPDATE test SET title = 'dont delete this' WHERE id IN(1,2,3);
UPDATE test SET deleted = true WHERE id NOT IN last_updated_rows('id');

另外，有没有办法在单个更新语句中执行这样的查询？

Answer 1

returning 子句专门做你想做的事。但是您问题中的查询可以用这样的逻辑处理:

UPDATE test
    SET title = (case when id in (1, 2, 3) then 'dont delete this' else title end),
        delete = (case when id in (1, 2, 3) then delete else true end);