在UPDATE的SET子句中使用GROUP BY出现子查询返回多行错误如何解决

错误原因 #

你编写的子查询通过GROUP BY f2会返回所有f2分组对应的计数结果，总共有多个行，而UPDATE语句的SET赋值要求子查询只能返回1行1列的标量值，因此触发多行返回报错。

修复方案 #

在子查询中添加和主表的关联条件，让每个主表行对应的子查询只返回当前行f2值匹配的计数结果，通用写法如下：

UPDATE 你要更新的实际表名 t1
SET TOTAL = (
    SELECT COUNT(f1)
    FROM TABLE2 t2
    WHERE t2.f2 = t1.f2
);

如果你使用的是支持JOIN更新的数据库（如MySQL、PostgreSQL等），也可以使用关联更新写法，批量处理时性能更优：

-- 关联更新示例
UPDATE 你要更新的实际表名 t1
JOIN (
    SELECT f2, COUNT(f1) AS f1_cnt
    FROM TABLE2
    GROUP BY f2
) t2 ON t1.f2 = t2.f2
SET t1.TOTAL = t2.f1_cnt;

注意事项 #

• 如果主表中存在f2值在TABLE2中没有匹配数据的情况，第一种通用写法会将对应行的TOTAL更新为NULL，如果需要默认赋值为0，可以用COALESCE函数处理：SET TOTAL = COALESCE((SELECT COUNT(f1) FROM TABLE2 t2 WHERE t2.f2 = t1.f2), 0)
• 执行更新操作前建议先通过SELECT语句验证统计结果是否符合预期，避免误改数据。

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