Calculating a running balance to store with transactions I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements.

Balance
| id | member_id | product_id | balance |
|  1 |    100001 |         33 |  975.00 |
|  1 |    100002 |         15 |   10.43 |
|  1 |    100001 |         15 |    0.00 |

Transactions
| id | member_id | product_id |       date |    value | interest |
|  1 |    100001 |         33 | 2014-09-01 |  -100.00 |     0.00 |
|  2 |    100001 |         33 | 2014-08-20 |   600.00 |     0.00 |
|  1 |    100002 |         15 | 2014-06-01 |   -50.00 |     0.00 |
|  2 |    100001 |         15 | 2014-08-20 |  -600.00 |     0.00 |


This data is read-only, and due to external issues with the legacy system the data is coming from the tables are emptied and everything re-inserted every time the data is updated. Not ideal, and the legacy system should be replaced within 18 months.

The transaction data I import doesn't have a running balance column. I need this, and have come up with two options:


Do not store a calculated balance against each transaction, but calculate on-the-fly. This makes paginating for account statements a pain, as no matter how far in the past we have to start with the latest balance and work back through all the transaction history. And I know no fast way to do this recursive query.
Calculate a running balance when transactions are imported, and store this against each row.


The second is my preferred option (for ease when extracting "March 2009's statement"). I am stuck trying to devise an efficient way to update the running balance: the best I have is code selecting all transactions for a particular user's account, then looping over them updating the figures. This is not performant.

How would you deal with this? Mine has to work on MySQL but I am also interested in seeing more elegant solutions than it supports.
Attemping an answer based on comments

The easiest and fastest way to do this is to take a set based approach. Like this:

1) Read NOW() into a variable @N

2) Load new data into Transactions

3) Update the Balance like this:

UPDATE Balance B
INNER JOIN (
  SELECT SUM(value) as value, member_id, product_id 
  FROM Transactions 
  WHERE date &gt;= @N) AS T
ON B.member_id = T.member_id
AND B.product_id = T.product_id
SET B.balance = B.balance + T.value


4) Profit