UPDATE语句触发ORA-01427错误：单行子查询返回多行问题求助

错误原因 #

原UPDATE语句的子查询未与外层table1做关联，而是直接关联两张表返回所有满足条件的行集合，导致每条待更新的table1行对应子查询的多行结果，触发"单行子查询返回多行"错误。

正确写法 #

场景1：table1.level1与table2.level2一一对应 #

使用关联子查询，确保每条table1行仅匹配唯一table2行：

UPDATE table1 t1
SET t1.sample1 = (SELECT t2.sample2
                  FROM table2 t2
                  WHERE t2.level2 = t1.level1)
WHERE t1.sample1 IS NULL
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.level2 = t1.level1);

• EXISTS子句用于过滤掉table2中无匹配的table1行，避免将这些行的sample1更新为NULL（可根据需求移除）。

场景2：table1.level1对应多个table2.level2行 #

若存在一对多匹配，需指定取哪条table2的数据，例如取sample2最大值：

UPDATE table1 t1
SET t1.sample1 = (SELECT MAX(t2.sample2)
                  FROM table2 t2
                  WHERE t2.level2 = t1.level1)
WHERE t1.sample1 IS NULL
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.level2 = t1.level1);

或用ROW_NUMBER()筛选指定排序的唯一行：

UPDATE table1 t1
SET t1.sample1 = (SELECT t2.sample2
                  FROM (SELECT t2.sample2,
                               ROW_NUMBER() OVER (PARTITION BY t2.level2 ORDER BY t2.sample2) rn
                        FROM table2 t2) t2
                  WHERE t2.level2 = t1.level1
                  AND t2.rn = 1)
WHERE t1.sample1 IS NULL
AND EXISTS (SELECT 1 FROM table2 t2 WHERE t2.level2 = t1.level1);

• PARTITION BY t2.level2按匹配字段分组，ORDER BY可指定排序规则（如时间、字段大小），rn=1取每组第一行。

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