Search code examples
mysqlsum

mysql : do calculation by user_id and store the value in another table

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
Solution 
  • 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