Update null values by value in same column

Question

I have a table in MS SQL Server, where are some null values in column "value"

Group   ID    Value
A       1     10
A       2     
A       3     
A       4     40
B       1     
B       2     20
B       3     30
B       4          

I want to update null values by not null in the same group with with the first higher ID, or if there is not any higher in same group, first lower. So the result should look like this.

Group   ID    Value
A       1     10
A       2     40
A       3     40
A       4     40
B       1     20
B       2     20
B       3     30
B       4     30 

Thanks!

Answer 1

You can use windowed version of SUM function in order to determine islands of NULL valued records along with the record having the higher ID in the same group:

SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp
FROM mytable

Output:

Group   ID  Value   grp
-----------------------
A       4   40      1
A       3   30      2
A       2   NULL    2
A       1   NULL    2
B       4   40      1
B       3   NULL    1
B       2   20      2
B       1   10      3

You can now wrap the above query in a CTE and use another CTE to do the update:

;WITH CTE AS (
SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp
FROM mytable
), ToUpdate AS (
   SELECT [Group], ID, Value,  
          MAX(Value) OVER (PARTITION BY [Group], grp) AS group_value  
   FROM CTE
)
UPDATE ToUpdate
SET Value = group_value
WHERE Value IS NULL

Demo here

Edit:

The above query doesn't handle the edge case where the very last record within a Group slice is NULL. To handle this case as well you can use the following query:

;WITH CTE AS (
SELECT [Group], ID, Value, 
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID DESC) AS grp,
       SUM(CASE WHEN Value IS NULL THEN 0 ELSE 1 END) OVER 
       (PARTITION BY [Group] ORDER BY ID) AS grp2
FROM mytable
), ToUpdate AS (
   SELECT [Group], ID, Value,            
          MAX(Value) OVER (PARTITION BY [Group], grp) AS group_value,  
          MAX(Value) OVER (PARTITION BY [Group], grp2) AS group_value2  
   FROM CTE
)
UPDATE ToUpdate
SET Value = COALESCE(group_value, group_value2)
WHERE Value IS NULL

Demo here

Answer 2

Please try this-

DATA GENERATION

DECLARE @T TABLE
(
    GroupCd CHAR(1),
    Id INT,
    Value INT
)

INSERT INTO @T
VALUES('A',1,10),
('A',2,NULL),
('A',3,NULL),
('A',4,40),
('B',1,NULL),
('B',2,20),
('B',3,30),
('B',4,NULL)

SOLUTION

UPDATE a
SET a.Value = b.Value
FROM @T a
INNER JOIN 
( 
    SELECT a.GroupCd,a.Id,Coalesce(a.Value,z.Value,z1.Value) Value 
    FROM @T a
    OUTER APPLY 
    (
        SELECT TOP 1 Value
        FROM @T b
        WHERE a.GroupCd = b.GroupCd
        AND b.Value IS NOT NULL AND a.Id < b.Id
        ORDER BY Id 
    )z
    OUTER APPLY 
    (
        SELECT TOP 1 Value
        FROM @T b
        WHERE a.GroupCd = b.GroupCd
        AND b.Value IS NOT NULL AND a.Id > b.Id
        ORDER BY Id DESC
    )z1
)b ON a.GroupCd = b.GroupCd AND a.Id = b.Id

SELECT * FROM @T

OUTPUT

GroupCd Id          Value
------- ----------- -----------
A       1           10
A       2           40
A       3           40
A       4           40
B       1           20
B       2           20
B       3           30
B       4           30

(8 rows affected)

Answer 3

You Can try This simple Method

DECLARE @T TABLE
(
    GroupCd CHAR(1),
    Id INT,
    Value INT
)

INSERT INTO @T
VALUES('A',1,NULL),
('A',2,NULL),
('A',3,30),
('A',4,40),
('B',1,10),
('B',2,20),
('B',3,NULL),
('B',4,40)

SELECT
    *,
    NewVal = COALESCE(Value,(SELECT TOP 1 Value FROM @T WHERE GroupCd = T.GroupCd AND Id > T.Id AND Value IS NOT NULL ORDER BY Id ASC))
    FROM @T T

My Result