Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
Both works like a charm. Especially second query is fantastic. Thanks everyone. –  hersly Mar 19 '13 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.

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.

share|improve this answer
    
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 Mar 18 '13 at 21:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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