Oracle SQL如何将多列最大值存入指定列 解决UPDATE多行列返回错误

错误原因分析 #

你写的UPDATE语句中嵌套的子查询没有和外层待更新的行做关联，子查询会返回表中所有行的计算结果，多个值无法赋值给单行的total字段，因此触发“请求返回多行”报错。即使添加rownum <=1限制，也只会把第一行的最大值赋值给全表所有行的total字段，完全不符合逐行计算当前行多列最大值的需求。

修正方案 #

最优写法（无需子查询） #

Oracle的GREATEST函数支持直接取当前行多列的最大值，直接逐行赋值即可，写法最简洁性能也最好：

UPDATE my_table SET total = GREATEST(col_1, col_2, col_3);

可选子查询写法（仅作参考，不推荐） #

如果业务场景必须使用子查询实现，需要通过行唯一标识关联内外层查询：

UPDATE my_table t1 SET total = (
  SELECT GREATEST(col_1, col_2, col_3)
  FROM my_table t2
  WHERE t2.ROWID = t1.ROWID
);

注意事项 #

如果col_1/col_2/col_3中存在NULL值，GREATEST会直接返回NULL，如果你需要忽略NULL值计算有效值的最大值，可以搭配NVL函数处理：

-- 把NULL替换为0后再计算最大值，可根据业务需求调整默认值
UPDATE my_table SET total = GREATEST(NVL(col_1,0), NVL(col_2,0), NVL(col_3,0));

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