Update column with the sum of column in previous rows in MySQL
Let make the table called nuoji and load it with the above data

USE test
DROP TABLE IF EXISTS nuoji;
CREATE TABLE nuoji
(
  id int not null auto_increment primary key,
  amount INT NOT NULL,
  balance INT NOT NULL,
  account INT NOT NULL,
  KEY (account)
) ENGINE=MyISAM;
INSERT INTO nuoji (amount,balance,account) VALUES
(100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
SELECT * FROM nuoji;


Let's run that code and Make the Data:

mysql&gt; USE test
Database changed
mysql&gt; DROP TABLE IF EXISTS nuoji;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; CREATE TABLE nuoji
    -&gt; (
    -&gt;   id int not null auto_increment primary key,
    -&gt;   amount INT NOT NULL,
    -&gt;   balance INT NOT NULL,
    -&gt;   account INT NOT NULL,
    -&gt;   KEY (account)
    -&gt; ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql&gt; INSERT INTO nuoji (amount,balance,account) VALUES
    -&gt; (100,100,1),(100,100,2),(-40,-40,1),(100,60,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |     -40 |       1 |
|  4 |    100 |      60 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql&gt;


Here is the Query You Need

SET @rt=0; SET @ndx=0; SET @given_account=1;
UPDATE nuoji A INNER JOIN  (SELECT * FROM
(SELECT (@ndx:=@ndx+1) ndx,id,amount,
(@rt:=@rt+amount) newbalance FROM nuoji
WHERE account=@given_account) BB) B USING (id)
SET A.balance=B.newbalance;


Here is that Query Executed:

mysql&gt; SET @rt=0; SET @ndx=0; SET @given_account=1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql&gt; UPDATE nuoji A INNER JOIN  (SELECT * FROM
    -&gt; (SELECT (@ndx:=@ndx+1) ndx,id,amount,
    -&gt; (@rt:=@rt+amount) newbalance FROM nuoji
    -&gt; WHERE account=@given_account) BB) B USING (id)
    -&gt; SET A.balance=B.newbalance;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql&gt; SELECT * FROM nuoji;
+----+--------+---------+---------+
| id | amount | balance | account |
+----+--------+---------+---------+
|  1 |    100 |     100 |       1 |
|  2 |    100 |     100 |       2 |
|  3 |    -40 |      60 |       1 |
|  4 |    100 |     160 |       1 |
+----+--------+---------+---------+
4 rows in set (0.00 sec)

mysql&gt;


That's it !!!

If other accounts are messed up in the same way, set @given_account, and then execute the code.

Here is That Code Formatted:

SET @rt = 0;
SET @ndx = 0;
SET @given_account = 1;
UPDATE
    nuoji A INNER JOIN
    (
        SELECT * FROM
        (
            SELECT
                (@ndx:=@ndx+1) ndx,id,
                amount,(@rt:=@rt+amount) newbalance
            FROM nuoji
            WHERE account=@given_account
        ) BB
    ) B USING (id)
SET A.balance=B.newbalance;


Give it a Try !!!