0

I have a system balance table "A", where the column "amount" is a debit/credit column, like this:

id amount balance 
1  100    100 
2  -30    70 
3  40     110
4  -20    90

If a new deposit is made of i.e. $50, the new row would have to be inserted as:

5  50      140

I could achieve this by selecting last balance:

SELECT id, balance
FROM A
ORDER BY id DESC LIMIT 1

and then inserting new row:

INSERT INTO A (amount, balance)
VALUES (50, previous_balance+50)

But, is there a way to achieve this with only one query? Would it be efficient on large databases? Thank you!

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
karlosuccess
  • 843
  • 1
  • 9
  • 25

3 Answers3

1

The correct way to do it is :

INSERT INTO A(amount,balance) 
VALUES (50, 50 + 
    (SELECT B.balance 
    FROM(SELECT balance FROM A ORDER BY id DESC LIMIT 1) AS B) 
);

Because mysql can't modify the same table which you use in the SELECT part

contremaitre
  • 106
  • 1
  • 2
  • 9
0

Just combine your 2 queries:

INSERT INTO A (amount, balance)
VALUES (50, (
   SELECT balance
   FROM (SELECT * from A)
   ORDER BY id DESC LIMIT 1
)+50)
Đào Minh Hạt
  • 2,742
  • 16
  • 20
  • I tried that but it is not working, mysql said `#1093 - You can't specify target table 'A' for update in FROM clause`, any thought please? – karlosuccess Mar 06 '17 at 09:27
  • Updated, please give it a try, refer to [this question](http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) – Đào Minh Hạt Mar 06 '17 at 09:30
  • Still not working, still throwing an error. @contremaitre posted the solution. Thank you anyways very much for your time. – karlosuccess Mar 06 '17 at 09:49
0

You can use this query:

INSERT INTO A (amount, balance)
  SELECT "50", SUM(balance)
    FROM ( SELECT id+1 as id, 50 balance FROM A
             UNION ALL
           SELECT id, balance FROM A 
             ORDER BY id DESC LIMIT 2
        ) t
Rafiqul Islam
  • 1,636
  • 1
  • 12
  • 25