How to Update a record Based on the the values on Previous row and Next Row-CodePudding

Below is the table with columns uniqueid Number, Complete date, and User id. The data below is sorted by userid, Complete date.

I want to update the Number Column where Uniqueid =456 to 'xxxx 1111' if the previous row and the row after null are having the same Number. If the Previous row or row after is null or different then no update.

Basically, I want to order the rows by userid, Complete date, identify the Rows that are null and check the previous row and row after the null record and update the null record with the same number the previous row and row after is having. if both don't match just leave it NULL record blank. Thank you.

Original

UniqueId Number        Complete date                UserId
----------------------------------------------------------
123      xxxx 1111     2022-03-17 11:19:07.000      11011
456       NULL         2022-03-17 11:22:50.000      11011
789      xxxx 1111     2022-03-17 11:28:32.000      11011

Expected output:

UniqueId  Number       Complete date                UserId
----------------------------------------------------------
123     xxxx 1111      2022-03-17 11:19:07.000      11011
456     xxxx 1111      2022-03-17 11:22:50.000      11011
789     xxxx 1111      2022-03-17 11:28:32.000      11011

SQL Server 2017 - T-SQL

I have tried the partition, min, and max and was not able to find exactly what I want.

select 
    UserId, completedate, number,   
    newremarks = max(userid) over (partition by number order by userid, completedate)
from 
    foo
order by 
    userId, CompleteDate

I tried something like the code shown above.

CodePudding user response：

You could utilise lag & lead here and an updatable CTE:

with n as (
    select *, 
        Lag(number) over(partition by UserId order by [Complete date]) pn,
        Lead(number) over(partition by UserId order by [Complete date]) nn
    from t
)
update n set number = pn
from n
where pn = nn and number is null;

See Demo Fiddle