I am trying to do SUM calculation to a record table by two constraints(user_id and symbol), and store the value into another table called calcultion.I try the following but it doesn't work.Can anyone give me some advice?
UPDATE records, calculation
SET calculation.sumOfAmount=sum(records.amount)
WHERE records.user_id=calculation.user_id AND records.symbol=calculation.symbol
CodePudding user response:
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