为何使用CTE的UPDATE语句会更新所有行？Postgres 12问题

你的错误出在UPDATE语句没有建立主表和CTE的关联条件。Postgres的UPDATE ... FROM语法里，如果不指定关联逻辑，会把task表的每一行和CTE里的所有行做笛卡尔积匹配——只要CTE有数据，task的所有行都会被更新，完全无视CTE里的LIMIT 100限制，这就是为什么会更新全部1000行。

修复后的两种正确写法： #

写法1：用WHERE IN关联

WITH cte AS (
    SELECT id
    FROM task
    WHERE executed = 0
    ORDER BY id DESC
    LIMIT 100
)
UPDATE task t
SET executed = 1
WHERE t.id IN (SELECT id FROM cte)
RETURNING t.id;

写法2：在FROM里显式关联（性能更优）

WITH cte AS (
    SELECT id
    FROM task
    WHERE executed = 0
    ORDER BY id DESC
    LIMIT 100
)
UPDATE task t
SET executed = 1
FROM cte
WHERE t.id = cte.id -- 核心：添加id匹配条件
RETURNING t.id;

额外优化建议： #

• CTE里不需要选executed列，只需要id用来关联即可，减少不必要的数据读取
• 确保task表的id列有主键或唯一索引，executed列建议加普通索引，这样CTE的筛选和UPDATE的关联都会更快，处理10万行时不会出现秒级延迟

内容的提问来源于stack exchange，提问作者sirjay