MySQL : Updating table with values from another table

Issue

Here I’m trying to update my users table with values from the transactions table in MySQL.

UPDATE users 
SET users.balance=(users.balance+(SUM(transaction.t_qty * transactions.share_price))) 
 FROM users,transactions 
 WHERE users.user_email=transactions.user_email;

Above are the two tables I am using for this query.
Kindly help me resolve this issue 🙂

I was expecting the values to get updated, it gave me a syntax error which I’m unable to rectify.

Kindly help me resolve this issue 🙂

Solution

First aggregate in transactions to get each user’s balance and then use a join of users to the results of the aggregation.

This is the correct syntax for MySql:

UPDATE users AS u
INNER JOIN (
  SELECT user_email,
         SUM(t_qty * share_price) AS balance
  FROM transactions
  GROUP BY user_email 
) AS t
ON t.user_email = u.user_email;
SET u.balance = t.balance;

Answered By – forpas

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0