I have two tables in SQL both hold the ID of users.
Table Accounts
x
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.
Advertisement
Answer
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
.