Using value from previous row

Question

I'm having some problem with using value from previous row (i kinda don't know how to do it).

I want to do something like :

 update test SET amount = (select amountAfter 
 from rozl (Here i want to take it from previous row = this ID - 1)

I got primary key id, whole table is sorted by id. What I want to do is :

Take value from previous row from Column name amountAfter and insert it into amount in actual id.

Here is example

    id  amount   used   destroyed  amountAfter
     1     100     50          30          20
     2      20      5           1          14

Answer 1

Here's one option using a join assuming your id fields are sequential:

update t1
set t1.amount = t2.amountafter
from test t1 join
     test t2 on t2.id = t1.id - 1

---

If you are using 2012 or higher, look at using lag:

with cte as (
    select id, amount, lag(amount) over (order by id) prevamount
    from test
    )
update cte 
set amount = prevamount 
where prevamount is not null

---

And I guess to be complete, 2008 would work with row_number if the numbers aren't sequential (using a combination of both approaches):

with cte as (
    select id, amount, row_number() over (order by id) rn
    from test
    )
update t1
set t1.amount = t2.amount
from cte t1 join
     cte t2 on t2.rn = t1.rn - 1