CODEWITHSUNDEEP
sql-servert-sqlnullgroup-byupdates
user918967
user918967

Reputation: 2167

TSQL update NULLs only when between the same values

I have a table where I need to fill in the NULLs with data. However, I only want to replace the NULL if the before and after values are the same.

G  ID   Val    NewValue
a   1   N       N
a   2   N       N
a   3   NULL    NULL   -- notice this is the last value of group 'a' so keep NULL and if NULL was the first of the group, keep NULL as well

b   4   P       P
b   5   N       N
b   6   NULL    N     -- N before and N after so the new value ='N'
b   7   N       N

c   8   N       N
c   9   N       N
c   10  NULL    NULL  -- N before but P after so keep NULL
c   11  P       P
c   12  N       N
c   13  N       N
c   14  N       N

d   15  P       P
d   16  NULL    P    --  P before and P after (the series) so make 'P'
d   17  NULL    P    --  P before and P after (the series) so make 'P'
d   18  P       P
d   19  N       N

So what I have a solution for the easy part. The query below gives:

So my question is how to do the rest?

DECLARE @Table TABLE(
    G varchar(1),
    ID INT,
    Val varchar(2)
    )
INSERT INTO @Table VALUES 
('a',1,'N'),('a',2,'N'),('a',3, NULL),
('b',4,'P'),('b',5,'N'),('b',6, NULL),('b',7,'N'),
('c',8, 'N'),('c',9, 'N'),('c',10,NULL),('c',11,'P'),('c',12,'N'),('c',13,'N'),('c',14,'N'),
('d',15, 'P'),('d',16, NULL),('d',17,NULL),('d',18,'P'),('d',19,'N')
SELECT *,
CASE WHEN Val IS NULL 
     THEN (SELECT TOP 1 Val FROM @Table WHERE ID<T.ID AND G=T.G AND Val IS NOT NULL ORDER BY ID DESC) 
     ELSE Val END AS NewVal
FROM @Table T

ORDER BY G, ID

Upvotes: 3

Views: 51

Answers (2)

Roger Wolf
Roger Wolf

Reputation: 7692

The most important thing when you solve problems is to understand the approach in plain English. Here, for example, all you need is to find two closest non-NULL values, the most preceding and the least subsequent. When they exist and are equal, use their value.

Here is a translation of this approach into SQL:

select t.*, isnull(t.Val, case when pr.Val = nr.Val then pr.Val end) as [NewVal]
from @table t
    outer apply (
        select top (1) tp.Val from @table tp
        where tp.G = t.G and tp.Val is not null and tp.Id < t.Id
        order by tp.Id desc
    ) pr
    outer apply (
        select top (1) tn.Val from @table tn
        where tn.G = t.G and tn.Val is not null and tn.Id > t.Id
        order by tn.Id
    ) nr
order by t.g, t.Id;

Upvotes: 1

S3S
S3S

Reputation: 25112

This will get you what you need minus the last one which you'd need to explain the logic and how you'd handle that.

SELECT
    t.G,
    t.ID,
    val,
    case 
        when val is null and 
             lag(val) over (partition by G order by ID) = lead(val) over (partition by G  order by ID) then lag(val) over (partition by G order by ID) 
        else val 
    end as NewValue
FROM @Table T

Upvotes: 1

Related Questions