SQL syntax question

Question

SQL syntax question about UPDATE. It would be much easier to ask the question by giving example below:

**Payments Table**

ID  user_id  payment_due   payment_made 
1      3        10.0           5.0
1      3        10.0           10.0
1      9        20.0           20.0



**Balance Table**

ID  user_id    current_balance 
1      3            ???
2      9            ???

Let say I would like to update the current balance for specific user. What would be the correct and efficient SQL syntax to add all payment dues and subtract it from all payment made for a specific user?

In this case, current_balance for user '3' is 5.0 and for user '9' is 0.0

Answer 1

How about:

select
   ID,
   user_id,
   (sum(payment_due) - sum(payment_made)) current_balance
from
   PaymentsTable

group by
   ID,
   user_id

Answer 2

You need to use a subquery. Something like this should work:

UPDATE
    balance_table b
SET
    current_balance = (SELECT
            SUM(payment_due - payment_made)
        FROM
            payments_table p
        WHERE
            p.user_id = b.user_id)

Answer 3

To update your table, use a sub-query:

UPDATE Balance_Table b
SET current_balance =
  ( SELECT SUM( payment_due - payment_made )
    FROM Payments_Table p
    WHERE p.user_id = b.user_id
  )

Think about the update-approach though. Your Balance_Table will be out of sync as soon as Payments_Table changes, so you might be better off with actually selecting data from there.