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!

Giorgos Betsos · Accepted Answer · 2018-02-08 13:47:15Z

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

Pawan Kumar · Accepted Answer · 2018-02-08 08:34:13Z

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)

Jayasurya Satheesh · Accepted Answer · 2018-02-08 08:03:01Z

2

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

answered Feb 8, 2018 at 8:03

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Comments

Michał Turczyn · Accepted Answer · 2018-02-08 08:45:10Z

0

update MY_TABLE set [value] = [newValue] from (
    select [Group] [newGroup],
           [Value] [newValue]
    from (
        select [Group], [Value],
               row_number() over (partition by [group] order by [Id] desc) [rn]
       from MY_TABLE
       where [Value] is not null
    ) [a] where [rn] = 1
) where [Group] = [newGroup] and [Value] is null

edited Feb 8, 2018 at 8:45

answered Feb 8, 2018 at 8:23

Michał Turczyn

41.2k18 gold badges57 silver badges87 bronze badges

2 Comments

Giorgos Betsos Over a year ago

The inner query will select just the record having the biggest Id value within each Group partition. This may be a NULL value, or a not relevant value, since a NOT NULL record can immediately follow this record.

Giorgos Betsos Over a year ago

It is still wrong. Try executing the UPDATE against the sample data provided.

Collectives™ on Stack Overflow

Update null values by value in same column

4 Answers 4

Comments

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Related