i have three column in my transaction table id int, amount int, balance int. I want to write a query when I insert new row balnce column automatically filled by previous row value +(-) this row value.

Question

I want to to this in a single query.

Answer 1

If you want to fill a new row with the last row values then it will be this way
insert into transaction (select max(id)+1,amount,balance
from transaction where id in( select max(id) from transaction));

if you want you can follow my Youtube channel rotkcraftlearning and if you want a video for an example that you want

Answer 2

thanks Jose for ur reply,

this is fine but what if I want use only one column value from previous row?

I am trying,
insert into transaction (amount, balance) values (1000, (select balance from transaction where id in (select max(id) from transaction))+1000);

but not working, showing CANT SPECIFY TARGET TABLE 'TRANSACTION' FOR UPDATE IN FROM CLAUSE

Answer 3

you want only one column value from last row try this way

insert into transaction(amount,balance) values(1000,(select * from(select balance from transaction where id in (select max(id) from transaction)) as lastamount) +1000);