4

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

)

1
  • 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
    Commented Jan 8, 2014 at 22:03

4 Answers 4

1

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;
3

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

2
  • So your code updates only given account but it is possible to do all the work at once :)
    – Kondybas
    Commented Jan 8, 2014 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. Commented Jan 8, 2014 at 18:44
3
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;
2
  • I ran this against original dataset and it works great without specifying the account. +1 !!! Commented Jan 8, 2014 at 18:59
  • Main trick is here: @a = @a := w.account
    – Kondybas
    Commented Jan 8, 2014 at 19:14
2

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 |
1
  • I ran this against original dataset and it works great without specifying the account. +1 !!! Commented Jan 8, 2014 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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