PostgreSQL更新数据表出现重复值问题求助

初始数据表 #

data表数据： #

postgres=# SELECT * from data;
 a  |     b     |         c
----+-----------+-------------------
 10 | some_text | some_text
 11 | some_text | some_text
 12 | some_text | some_another_text
 13 | some_text | some_another_text
(4 rows)

临时表tmp数据： #

postgres=# SELECT * FROM tmp;
 a  |     b     |     c
----+-----------+-----------
 21 | some_text | some_text
 22 | some_text | some_text
(2 rows)

执行的UPDATE语句 #

UPDATE data SET a = tmp.a, b = tmp.b, c = tmp.c FROM tmp WHERE data.c = 'some_another_text';

预期结果 #

postgres=# SELECT * from data;
 a  |     b     |         c
----+-----------+-------------------
 10 | some_text | some_text
 11 | some_text | some_text
 21 | some_text | some_text
 22 | some_text | some_text
(4 rows)

实际结果 #

postgres=# SELECT * FROM data;
 a  |     b     |     c
----+-----------+-----------
 10 | some_text | some_text
 11 | some_text | some_text
 21 | some_text | some_text
 21 | some_text | some_text

问题原因 #

你的UPDATE语句只指定了data.c = 'some_another_text'作为过滤条件，但没有定义data表和tmp表之间的关联匹配规则。这会导致所有符合data.c = 'some_another_text'的行，和tmp表的所有行形成笛卡尔积（也就是2行data × 2行tmp = 4条匹配记录）。PostgreSQL处理这种情况时，会对每个data行保留最后一次匹配的tmp行数据来执行更新，最终所有符合条件的data行都会被更新成同一个tmp行的值（这里就是a=21的那一行）。

你期望的是两行目标数据分别对应tmp的两行，但当前语句没有告诉数据库如何一一对应这两组数据，所以无法达到预期效果。

正确写法 #

如果需要将tmp的行依次更新到data中符合条件的行，可以通过给两组数据添加行编号，再按编号关联的方式实现：

WITH numbered_data AS (
    SELECT *, ROW_NUMBER() OVER () AS rn
    FROM data
    WHERE c = 'some_another_text'
),
numbered_tmp AS (
    SELECT *, ROW_NUMBER() OVER () AS rn
    FROM tmp
)
UPDATE data
SET a = nt.a, b = nt.b, c = nt.c
FROM numbered_data nd
JOIN numbered_tmp nt ON nd.rn = nt.rn
WHERE data.a = nd.a AND data.b = nd.b AND data.c = nd.c;

注意：行编号的顺序依赖于数据库的查询执行计划，若有明确的业务逻辑对应关系（比如原data的a字段和tmp的a字段有映射规则），建议用业务字段作为关联条件，这样结果更稳定。

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