how to carry forward previous row value in mysql

Question

I have a table called installment which contains one column installment, and have an altered the table and added one more column called previous_instalment it contains 0 by default. Up to this, it is working fine

id|intalment|prev_instalment|
- |-------- |---------------|
1 |7500     |       0       |
2 |1500     |       0       |
3 |850      |       0       |
4 |900      |       0       | 

Now I want my previous_instalment column should be like this

id|intalment|prev_instalment|
- |-------- |---------------|
1 |7500     |       0       |
2 |1500     |      7500     |
3 |850      |      1500     |
4 |900      |       850     |
and so on 

I want output as above

I use this query

select * from tmp_tst ;
UPDATE tmp_tst as ud 
JOIN(

    select id,ifnull(added_instalments - lag(prev_instalment) OVER (ORDER BY Id),0) as tf FROM tmp_tst
) as t
on ud.id=t.id
set ud.prev_instalment=t.tf;
select * from marketing.tmp_tst;

it showing me the wrong result

Answer 1

Consider the following select query:

select id, instalement, 
    lag(instalment, 1, 0) over(order by id) prev_instalment
from mytable

We can easily turn this to an update / join:

update mytable t
inner join (
    select id, lag(instalment, 1, 0) over(order by id) prev_instalment
    from mytable 
) x on x.id = t.id
set t.prev_instalment = x.prev_instalment