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.

We have a table, essentially:

id, amount, end balance, account_id

I now want to re-calculate the end balance values (because of possible corruption)

End balance is basically "end balance of previous entry with the same account_id" + amount.

Assume that there are a lot of entries, but I want to do this for one account at a time (or for a couple of accounts).

(So, if we have the corrupt table:

Id  Amount Balance Account
1     100       0     1
2     100     100     2
3     -40     -40     1
4     100      60     1

I want the resulting db table to be:

Id  Amount Balance Account
1     100     100     1
2     100     100     2
3     -40      60     1
4     100     160     1

)

share|improve this question
    
Thank you for all your answers. They all work but unfortunately I can't award all the right answer. I ended up selecting the query that in the simplest and most straightforward manner solved the question. –  Nuoji Jan 8 at 22:03

4 Answers 4

up vote 1 down vote accepted

Solution that fix one account at a time:

SET @b = 'z';
UPDATE table AS w
    SET w.balance = IF(@b='z', @b:=w.amount, @b:=@b+w.amount )
    WHERE w.account = 1
    ORDER BY w.id;
share|improve this answer

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> USE test
Database changed
mysql> DROP TABLE IF EXISTS nuoji;
Query OK, 0 rows affected (0.00 sec)

mysql> 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;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO nuoji (amount,balance,account) VALUES
    -> (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> 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>

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> 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> 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;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> 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>

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 !!!

share|improve this answer
    
So your code updates only given account but it is possible to do all the work at once :) –  Kondybas Jan 8 at 18:25
    
@Kondybas the original question says Assume that there are a lot of entries, but I want to do this for one account at a time (or for a couple of accounts).. So, my answer addresses fixing only one account. –  RolandoMySQLDBA Jan 8 at 18:44

Here's my try. I made it so you can update the table all at once, you don't have to specify the account id separately. I also added a bit more sample data.

Sample data:

CREATE TABLE t
    (`Id` int, `Amount` int, `Balance` int, `Account` int)
;

INSERT INTO t
    (`Id`, `Amount`, `Balance`, `Account`)
VALUES
    (1, 100, 0, 1),
    (2, 100, 100, 2),
    (3, -40, -40, 1),
    (4, 100, 60, 1),
    (5, 50, 25, 2),
    (6, 75, 30, 3)
;

Query:

update t inner join (
select
t.*
, @prevAmount := if(@prevAccount != Account, 0, @prevAmount)
, Amount + @prevAmount AS new_balance
, @prevAmount := Amount + @prevAmount
, @prevAccount := Account
from
t
, (select @prevAmount := 0, @prevAccount := (select min(account) from t)) v
order by Account, Id
) sq on t.Id = sq.Id
set t.Balance = sq.new_balance;

Result:

select * from t
order by Account, Id;

| ID | AMOUNT | BALANCE | ACCOUNT |
|----|--------|---------|---------|
|  1 |    100 |     100 |       1 |
|  3 |    -40 |      60 |       1 |
|  4 |    100 |     160 |       1 |
|  2 |    100 |     100 |       2 |
|  5 |     50 |     150 |       2 |
|  6 |     75 |      75 |       3 |
share|improve this answer
    
I ran this against original dataset and it works great without specifying the account. +1 !!! –  RolandoMySQLDBA Jan 8 at 18:55
SET @a = 'z', @b = 'z';

UPDATE table AS q
    JOIN 
    (
        SELECT w.id
              ,w.amount
              ,w.balance
              ,w.account
              ,IF(@a = @a := w.account, @b := @b, @b := 0 ) AS foo
              ,IF(@b='z', @b := w.amount, @b := @b + w.amount) AS newbalance
            FROM table AS w
            ORDER BY w.account, w.id
    ) AS z ON q.id = z.id
    SET q.balance = z.newbalance;
share|improve this answer
    
I ran this against original dataset and it works great without specifying the account. +1 !!! –  RolandoMySQLDBA Jan 8 at 18:59
    
Main trick is here: @a = @a := w.account –  Kondybas Jan 8 at 19:14

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.