I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values.

id   tstamp                   amount   balance
 1   2013-03-18 00:00:10       57.00      0.00
 2   2013-03-18 00:00:11       13.05      0.00
 3   2013-03-18 00:00:12      110.00      0.00
 4   2013-03-18 00:00:13       23.50      0.00
 5   2013-03-18 00:00:14       35.44      0.00
 6   2013-03-18 00:00:15       76.00      0.00
 7   2013-03-18 00:00:16       34.74      0.00
 8   2013-03-18 00:00:17      120.47      0.00
 9   2013-03-18 00:00:18       35.00      0.00
10   2013-03-18 00:00:09       46.00      0.00

so balance fields' values must be like that: current row's balance = CHRONOLOGICALLY previous row's balance + current row's amount.

Notice last row's tstamp value is smaller than first row's tstamp value. so when I say previous row I do not mean current id minus 1. So highest balance value must be at row #9.

And the problem is how to update all balances with chronogically previous row's balance value + current row's amount value?

2 Answers 2


Assuming that the tstamp has a UNIQUE constraint:

UPDATE activities AS a
  ( SELECT cur.tstamp,
           SUM(prev.amount) AS balance 
    FROM activities AS cur
      JOIN activities AS prev
        ON prev.tstamp <= cur.tstamp
    GROUP BY cur.tstamp
  ) AS p
  ON p.tstamp = a.tstamp
SET a.balance = p.balance ;

Tested: SQL-Fiddle

MySQL has also a feature to use ORDER BY with an UPDATE, which you can combine with the use of variables:

SET @b := 0 ;
UPDATE activities
SET balance = (@b := amount + @b)
ORDER BY tstamp ;

Tested: SQL-Fiddle

  • Both works like a charm. Especially second query is fantastic. Thanks everyone.
    – hersly
    Commented Mar 19, 2013 at 11:14

I'm AFK, and I can't read my SSH client on my phone, so I haven't tested this, but this is adapted from a similar scenario I have, although it's a SELECT rather than an UPDATE. IOW, test it out, but this should get you on the right track:

UPDATE activities a1
SET a1.balance = a1.amount + (
    SELECT balance 
    FROM activities a2
    WHERE a2.tstamp < a1.tstamp
    ORDER BY a2.tstamp DESC
    LIMIT 1
) prev_balance;

My concern with this is that each balance has to be done in chronological order for this to work; with your example data, the row with id = 10 would have to be done FIRST, then the record with id = 1 etc. AFAIK you can't force the order in which an update will occur, so you may have to execute this update against each row, stepping through with a cursor.


The error is because you can't select directly from an update target in MySQL. I can't see a way to do this without a cursor, since you need to make sure the rows are updated in chronological order. Assuming this is an operation that you are performing only once, I'd read everything into a temp table in chronological order, then step through it with a cursor and update the table activities accordingly.

  • 1
    When i use exactly same query it gives a syntax error at 'prev_balance' part. When i remove this part then it gives that error: #1093 You can't specify target table 'a1' for update in FROM clause.
    – hersly
    Commented Mar 18, 2013 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.