StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to update a column value incrementally
text
Body
copied!<p>I'm experimenting with a personal finance application, and I'm thinking about what approach to take to update running balances when entering a transaction in an account.</p> <p>Currently the way I'm using involves retrieving all records more recent than the inserted/modified one, and go one by one incrementing their running balance.</p> <p>For example, given the following transactions:</p> <p>t1 date = 2008-10-21, amount = 500, running balance = 1000</p> <p>t2 date = 2008-10-22, amount = 300, running balance = 1300</p> <p>t3 date = 2008-10-23, amount = 100, running balance = 1400 ...</p> <p>Now suppose I insert a transaction between t1 and t2, then t2 and all subsequent transactions would need their running balances adjusted.</p> <p>Hehe, now that I wrote this question, I think I know the answer... so I'll leave it here in case it helps someone else (or maybe there's even a better approach?)</p> <p>First, I get the running balance from the previous transaction, in this case, t1. Then I update all following transactions (which would include the new one):</p> <pre><code>UPDATE transactions SET running_balance = running_balance + <AMOUNT> WHERE date > <t1.date> </code></pre> <p>The only issue I see is that now instead of storing only a date, I'll have to store a time too. Although, what would happen if two transactions had the exact same date/time?</p> <p>PS: I'd prefer solutions not involving propietary features, as I'm using both PostgreSQL and SQLite... Although a Postgre-only solution would be helpful too.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload