MySQL : Updating table with values from another table-CodePudding

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 :)

CodePudding user response：

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;