Home > Blockchain >  MySQL : Updating table with values from another table
MySQL : Updating table with values from another table

Time:11-08

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;

Transactions Table

Users Table

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.

Error Message

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;
  • Related