mysql:根据user_id进行计算，并将值存储在另一个表中

Question

我试图通过两个约束(user_id和symbol)对记录表进行SUM计算，并将值存储到另一个称为计算的表中。我尝试以下，但它不工作。谁能给我一些建议?

UPDATE records, calculation
SET calculation.sumOfAmount=sum(records.amount) 
WHERE records.user_id=calculation.user_id AND records.symbol=calculation.symbol

Answer 1

UPDATE calculation c
JOIN 
(
SELECT user_id, symbol, sum(amount) as sum_amount
FROM records
GROUP BY user_id, symbol
) r ON r.user_id = c.user_id AND r.symbol = c.symbol
SET c.sumOfAmount = r.sum_amount