PostgreSQL UPDATE子查询仅执行一次：如何按组仅更新一行

你的问题核心在于非关联子查询只会在UPDATE语句执行前一次性计算：初始状态下原表的name都是未处理的变体，处理后的标准化值自然不在子查询结果里，所以所有行都满足NOT IN条件，导致全部被更新。

要实现“同一单词的多个变体仅修改其中一个”，可以通过预先分组标记目标行的方式解决，以下是两种可行方案：

方案一：用窗口函数标记每组待更新行 #

通过窗口函数按标准化后的名字分组，给每组行排名，只更新每组的第一行（这里按ID排序选最小ID的行，可根据需求调整排序规则）：

WITH ranked_rows AS (
    SELECT 
        id,
        translate(trim(regexp_replace(name, '\s+', ' ', 'g')), 'ąćęłńóśźżĄĆĘŁŃÓŚŹŻ', 'acelnoszzACELNOSZZ') AS standardized_name,
        ROW_NUMBER() OVER (
            PARTITION BY translate(trim(regexp_replace(name, '\s+', ' ', 'g')), 'ąćęłńóśźżĄĆĘŁŃÓŚŹŻ', 'acelnoszzACELNOSZZ') 
            ORDER BY id
        ) AS rn
    FROM newtable
)
UPDATE newtable nt
SET name = rr.standardized_name
FROM ranked_rows rr
WHERE nt.id = rr.id
AND rr.rn = 1;

方案二：仅在无标准化值时更新对应组的一行 #

如果需求是“仅当原表中不存在标准化后的名字时，才从对应变体里选一行修改”，可以用以下语句：

WITH target_rows AS (
    SELECT 
        MIN(id) AS target_id,
        translate(trim(regexp_replace(name, '\s+', ' ', 'g')), 'ąćęłńóśźżĄĆĘŁŃÓŚŹŻ', 'acelnoszzACELNOSZZ') AS standardized_name
    FROM newtable
    GROUP BY standardized_name
    HAVING NOT EXISTS (
        SELECT 1 FROM newtable WHERE name = standardized_name
    )
)
UPDATE newtable nt
SET name = tr.standardized_name
FROM target_rows tr
WHERE nt.id = tr.target_id;

为什么原查询不生效？ #

PostgreSQL中，NOT IN后的非关联子查询会被优化为一次性执行，基于语句开始时的表快照。UPDATE过程中对表的修改不会被同语句的子查询感知，所以初始状态下没有标准化值，所有行都会被判定为满足条件。关联子查询也无法解决这个问题，因为同样受快照隔离限制。因此必须通过预分组的方式提前确定要更新的行。

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