Reports

This is a highly nonstandard schema for your table. Is it a requirement to create a new column when the user takes action in your application?

In SQL, it's preferred to maintain a relationship between data. For example, your example above could be modeled by a table with four columns: User, Day, Transaction Amount, Balance To Date.

From there, you could use a SELECT subquery to get the latest "Balance To Date" and use that to create a new row in the table for your user.

For example:

INSERT INTO account_balances (user, day, amount, balance)
SELECT 'username', 
        1, 
        100, 
        COALESCE(SELECT balance 
                 FROM account_balances 
                 WHERE user = 'username' 
                 ORDER BY day DESC 
                 LIMIT 1
                ), 0) + 100;

The COALESCE keyword is to handle the case where it's the first time inserting into the table for a given user.

79398746