0
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.
I want to to this in a single query.
3 odpowiedzi
+ 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
0
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
0
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);