基于select的MySQL更新

Question

SELECT 
   table_1.id, 
   (SELECT SUM(amount) FROM table_2 WHERE table_1.id = table_2.bid) AS sum_total 
FROM table_1 
WHERE table_1.total!=0

我需要更新 table_1.total 使其等于 sum_total。

有什么建议吗？

Answer 1

这个简单的查询应该工作得很好。

UPDATE 
   table_1 
SET 
   table_1.total = (SELECT SUM(amount) FROM table_2 WHERE table_1.id = table_2.bid) 
WHERE table_1.total!=0;