CODEWITHSUNDEEP
sqlsql-server
roncansan
roncansan

Reputation: 2380

SQL: how to update a row based on a previous row

I have the following table:

id - home id - home status - previous home status

1  - a - occupied - null

2  - a - abandoned - null 

3  - a - foreclosure - null 

4  - b - occupied - null  

5  - b - occupied - null 

6  - c - occupied - null

I want to generate an update statement that updates the table as follows:

1  - a - occupied - abandoned

2  - a - abandoned - foreclosure 

3  - a - foreclosure - null 

4  - b - occupied - occupied  

5  - b - occupied - null 

6  - c - occupied - null

I'm using the following algorithm:

set @id = 1
set @max_id = (select MAX(id) from home)

select @homeid = homeid from home where id = @id

while @id <= @max_id
begin

     select 
        @previous_homeid = home,
        @previous_home_status = @home_status
    from 
        home where id = (@id + 1)

    if(@previous_homeid = @homeid)
    begin
        update 
            home 
        set  
            home_status = @previous_home_status
        where 
            id = @id
    end

    set @homeid = @previous_homeid
    set @previous_homeid = null
    set @previous_home_status = null
    set @id = @id + 1;
end

Is there a more efficient algorithm? I don't like the loop efficiency

Upvotes: 0

Views: 3538

Answers (2)

Thom A
Thom A

Reputation: 96055

This is untested, but should get you what you're after:

WITH CTE AS(
    SELECT id, [home id], [home status], [previous home status],
           LEAD([previous home status]) OVER (PARTITION BY [home id] ORDER BY id ASC) AS NextStatus
    FROM home)
UPDATE CTE
SET [previous home status] = NextStatus;

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use lead to get the value on the next row.

with cte as (select t.*
             ,lead(home_status) over(partition by home_id order by id) as next_home_status
             from tbl t)
update cte set previous_home_status=next_home_status

Upvotes: 2

Related Questions