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

Ron Savage · Accepted Answer · 2010-03-03 18:38:05Z

2

How about:

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

group by
   ID,
   user_id

answered Mar 3, 2010 at 18:38

Ron Savage

11.1k4 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

cmptrgeekken Over a year ago

This might be the best approach. Don't keep a Balance table since the balance would constantly change. You could even turn this query into a view so that it behaves like the Balance table.

Frank Heikens Over a year ago

Or use a trigger to keep the data in sync.

Leslie Over a year ago

as stated above don't keep a Balance table...storing a calculated field (current_balance) breaks the rules of normalization, use the query to get the data on the fly

Cory T. Over a year ago

Thanks all for the suggestions. I think it is best not to use current balance table

Peter Lang · Accepted Answer · 2010-03-03 18:38:58Z

2

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.

answered Mar 3, 2010 at 18:38

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

2 Comments

cmptrgeekken Over a year ago

I suppose he could throw this UPDATE statement in a trigger on the Payments table. That would remove some of the discrepancy.

Peter Lang Over a year ago

@cmptrgeekken: Using a trigger would be possible. If there are lots of records in Payments_Table but only few records change and results of Balance_Table are queried often, then this could be the right approach. Otherwise, just summing up Payments_Table would be the easier approach.

Tatu Ulmanen · Accepted Answer · 2010-03-03 18:38:14Z

1

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)

answered Mar 3, 2010 at 18:38

Tatu Ulmanen

125k34 gold badges190 silver badges185 bronze badges

Collectives™ on Stack Overflow

SQL syntax question

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related