CODEWITHSUNDEEP
sqlmysql
Cory T.
Cory T.

Reputation: 11

SQL syntax 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

Upvotes: 1

Views: 104

Answers (3)

Peter Lang
Peter Lang

Reputation: 55524

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.

Upvotes: 2

Tatu Ulmanen
Tatu Ulmanen

Reputation: 124768

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)

Upvotes: 1

Ron Savage
Ron Savage

Reputation: 11079

How about:

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

group by
   ID,
   user_id

Upvotes: 2

Related Questions