Search code examples
mysqlsqlsumsql-update

MySQL column to show the sum of column in another table

I have two tables in SQL both hold the ID of users.

Table Accounts

ID    username     Points
1       abc          ? 
2       def          ?

Table Transactions

ID    Points
1       10
1       -5
2       20

The table accounts column points should have the sum of that ID in table transactions. So in table one, the output should be points of ID 1 to be 5 and id 2 to be 20.

I am new to MySQL so if you could just explain the query a bit will be appreatiated.

Solution

  • You need an UPDATE statement:

    UPDATE Accounts a
SET a.Points = (SELECT SUM(t.Points) FROM Transactions t WHERE t.ID = a.ID)

    The correlated subquery returns the sum of points of each ID.