mysql - 使用修改后的另一个表中的信息更新表

Question

我想将数据(经过修改)从一个表移动到另一个表。

UPDATE table2 
SET val_new=(SELECT (table1.val_old - SUM(table3.val_2)) as some1 
FROM table3 
INNER JOIN table1 ON table3.id_some=.table1.id_some 
WHERE table3.id_typ<=2 
AND table3.id_status=1 
group by table3.id_some);

我有错误:

Error Code: 1242. Subquery returns more than 1 row

我必须做什么才能更正此查询？

Answer 1

最简单的修复方法是:

UPDATE table2 SET 
val_new=ifnull((
     SELECT (table1.val_old - SUM(table3.val_2)) as some1 
     FROM table3 
     INNER JOIN table1 ON table3.id_some=.table1.id_some 
     WHERE table3.id_typ<=2 
     AND table3.id_status=1 
     WHERE table3.table2_id = table2.id -- ADDED THIS LINE
     group by table3.id_some), val_new);