使用临时表更新student_subjects表时子查询返回多行报错如何解决

错误原因 #

SET子句中的标量子查询未添加与外层更新行的匹配条件，每次执行都会返回temp_marks全表数据，无法和待更新的student_subjects行一一对应，因此触发多行返回的报错。

解决方案 #

方案1：关联子查询适配版（兼容大部分SQL数据库） #

给标量子查询添加关联条件，确保每个待更新行仅匹配到唯一的temp_marks值：

UPDATE student_subjects
SET marks = (
    SELECT tmp.value::int
    FROM temp_marks AS tmp
    JOIN subjects s ON s.code = tmp.key
    WHERE s.id = student_subjects.subject_id
)
WHERE student_id = std_id 
  AND subject_id IN (
      SELECT id 
      FROM subjects 
      WHERE code IN (SELECT key FROM temp_marks)
  );

方案2：PostgreSQL专属优化版（性能更优） #

使用PostgreSQL支持的UPDATE ... FROM语法，直接关联多表完成更新，避免重复扫描表数据：

UPDATE student_subjects ss
SET marks = tmp.value::int
FROM temp_marks tmp
JOIN subjects s ON s.code = tmp.key
WHERE ss.student_id = std_id
  AND ss.subject_id = s.id;

注意事项 #

• 如果业务中存在temp_marks.key和subjects.code一对多匹配的情况，需要根据实际规则添加去重逻辑，例如使用DISTINCT、LIMIT 1或者聚合函数指定取值规则
• 执行更新操作前，建议先通过SELECT语句验证关联匹配结果，避免误改数据

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