PostgreSQL指定行更新异常求助：执行语句为何更新全部行？

这个问题我之前也碰到过，核心原因是你的UPDATE语句里主表news和子查询foo之间没有建立关联关系，导致了意外的笛卡尔积，最终更新了所有行。

为什么原语句会更新所有行？ #

在PostgreSQL的UPDATE ... FROM语法中，如果主表（这里是news）和FROM子句中的表（这里是foo）没有通过WHERE条件关联，数据库会生成两个表的笛卡尔积——也就是news的每一行都会和foo的每一行进行配对。

你的WHERE条件rownum = 1 or rownum = 15 or rownum = 32 or rownum = 54其实是在筛选foo中符合条件的行，但只要foo存在这些行，news的每一行都会和这些符合条件的foo行匹配，最终news的所有行都会被执行更新操作（哪怕同一行被多次更新，结果都是verified='t'）。

另外还要注意：ROW_NUMBER() OVER ()没有指定ORDER BY子句时，行的排序是不确定的，每次执行可能得到不同的行号对应关系，这会导致你更新的行不是你预期的目标行。

修复后的正确写法 #

方法1：用CTE+IN子句（推荐，逻辑更清晰） #

假设news表有主键字段（比如id），可以先通过CTE获取目标行号对应的主键，再更新：

WITH numbered_news AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rownum  -- 加上ORDER BY确保行号稳定
    FROM news
)
UPDATE news
SET verified = 't'
WHERE id IN (
    SELECT id FROM numbered_news WHERE rownum IN (1, 15, 32, 54)
);

方法2：在UPDATE ... FROM中添加关联条件 #

直接在WHERE子句中关联主表和子查询的主键，避免笛卡尔积：

UPDATE news
SET verified = 't'
FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rownum  -- 必须加ORDER BY保证行号稳定
    FROM news
) AS foo
WHERE news.id = foo.id AND foo.rownum IN (1, 15, 32, 54);

关键提醒 #

一定要给ROW_NUMBER() OVER ()加上明确的ORDER BY子句（比如ORDER BY id、ORDER BY create_time等），否则数据库会随机返回行的顺序，你每次执行可能更新不同的行，完全不符合预期。

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