CODEWITHSUNDEEP
mysqlsql
Thorben Croisé
Thorben Croisé

Reputation: 12870

MySQL: Update values with sum of two tables

I have a table setup like this

Liabilities:

Id | CustomerId | liabilities
---------------------------
9    90           1000
...

Payments:

Id | CustomerId | Payment
---------------------------
3    90           2500
4    91           1000
...

Customer:

Id | balance
---------------------------
90   1500
91   1000
...

As you can see, the balance for a customer is the sum of all its payments minus the sum of all its liabilities. What is an SQL query to update the balance?

Upvotes: 0

Views: 170

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do it using an UPDATE statement with LEFT JOIN operations to derived tables containing Payments and Liabilities aggregates:

UPDATE Customer AS c
LEFT JOIN (
  SELECT CustomerId, SUM(Payment) AS TotalPayment
  FROM Payments
  GROUP BY CustomerId
) AS p ON c.Id = p.CustomerId
LEFT JOIN (
  SELECT CustomerId, SUM(liabilities) AS TotalLiabilities
  FROM Liabilities
  GROUP BY CustomerId
) AS l ON c.Id = l.CustomerId  
SET balance = COALESCE(TotalPayment, 0) - COALESCE(TotalLiabilities, 0)

Demo here

Alternatively, you can use correlated subqueries in the UPDATE statement:

UPDATE Customer AS c 
SET balance = COALESCE((SELECT SUM(Payment) 
                        FROM Payments 
                        WHERE CustomerId = c.Id) , 0)
              - 
              COALESCE((SELECT SUM(liabilities) 
                        FROM Liabilities 
                        WHERE CustomerId = c.Id)  , 0)

Demo here

Upvotes: 2

Related Questions