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

• hersly: Both works like a charm. Especially second query is fantastic. Thanks everyone.

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.

• hersly: 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.