how to set a row's value from a certain row's value?

Question

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?

Answer 1

Assuming that the tstamp has a UNIQUE constraint:

UPDATE activities AS a
  JOIN
  ( 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

Answer 2

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.

EDIT

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.