PostgreSQL UPDATE子查询多行报错定位及语句改写问题

你执行UPDATE语句报错的核心原因是：SET子句中使用的关联子查询属于标量子查询，要求对schema1.table1中的每一行，子查询最多只能返回1行结果。当前存在至少一行schema1.table1记录，在schema1.table2中匹配到了多条符合过滤条件的记录，哪怕你加了DISTINCT，只要这些匹配记录的a1~a5字段组合不唯一，仍然会触发多行返回的错误。

你之前写的WITH语句无法定位错误行，因为它没有关联schema1.table1的行标识，也没有统计每行的匹配数量，所以返回的行数和原UPDATE涉及的251行无法对应。

1. 直接查询触发错误的schema1.table1行 #

运行以下语句可以直接得到所有匹配到≥2条schema1.table2记录的错误行，以及每个行匹配的记录数：

SELECT 
  t.id, 
  t.date1, 
  COUNT(*) AS matched_table2_row_count
FROM schema1.table1 t
INNER JOIN schema1.table2 tt 
  ON tt.id = t.id 
  AND tt.is_flg = 1
  AND tt.some_cnt > 0
  AND t.date1 BETWEEN tt.date1 AND tt.date2
GROUP BY t.id, t.date1
HAVING COUNT(*) > 1;

2. 查看错误行对应的所有匹配明细 #

如果需要查看这些错误行对应的schema1.table2匹配详情，方便后续判断应该选取哪条记录更新，可以运行以下语句：

SELECT 
  t.id AS table1_error_row_id, 
  t.date1 AS table1_row_date,
  tt.*
FROM schema1.table1 t
INNER JOIN schema1.table2 tt 
  ON tt.id = t.id 
  AND tt.is_flg = 1
  AND tt.some_cnt > 0
  AND t.date1 BETWEEN tt.date1 AND tt.date2
WHERE (t.id, t.date1) IN (
  SELECT t2.id, t2.date1
  FROM schema1.table1 t2
  INNER JOIN schema1.table2 tt2 
    ON tt2.id = t2.id 
    AND tt2.is_flg = 1
    AND tt2.some_cnt > 0
    AND t2.date1 BETWEEN tt2.date1 AND tt2.date2
  GROUP BY t2.id, t2.date1
  HAVING COUNT(*) > 1
)
ORDER BY t.id, t.date1;

定位到错误原因后，你可以根据业务逻辑选择匹配规则，避免子查询返回多行：

• 如果业务允许取任意一条匹配记录，可以在子查询末尾加LIMIT 1
• 如果需要取最新的匹配记录，可以在子查询中加ORDER BY tt.date2 DESC LIMIT 1
• 如果需要聚合计算取值，可以用MAX()/MIN()等聚合函数包裹返回字段，确保只返回一行结果

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