SQL SERVER Replace Null

Question

I have a tsql query like that:

DECLARE @TABLE TABLE(
    id int primary key identity(1,1),
    code int  );
INSERT INTO @TABLE VALUES (1),(NULL),(NULL),(NULL),(2),(NULL),(NULL),(NULL),(3),(NULL),(NULL),(NULL);
SELECT 
    id ,
    code
FROM @TABLE T1

My result :

id  code
1   1
2   NULL
3   NULL
4   NULL
5   2
6   NULL
7   NULL
8   NULL
9   3
10  NULL
11  NULL
12  NULL

I want to change null value with null value's last one numeric value. I want to see that:

But i dont want to use while loop. How can i do it ?

Gordon Linoff · Accepted Answer · 2020-10-30 14:46:59Z

2

If the values are increasing, use a cumulative max:

select t.*,
       max(code) over (order by id) as imputed_code
from @table t;

If the code is not strictly increasing, then you can do this in two steps:

select t.*, max(code) over (order by grp) as imputed_code
from (select t.*,
             count(code) over (order by id) as grp
      from @table t
     ) t;

Given that this is a table variable, I'm guessing that you don't really want to update it. But if you do:

with toupdate as (
      <one of the above queries>
     )
update toupdate
    set code = imputed_code
    where code is null;

answered Oct 30, 2020 at 14:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SERVER Replace Null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related