Updating SQL column value with previous row data that said to UPDATE a column to SET previous row value to NULL cells in SQL Server [duplicate]

Question

I have the table as

id  value
1   music
2 
3   movie
4   
5
6   book
7
8

For the missing rows, and I want to assign the value of previous row like listed below

id  value
1   music
2   music
3   movie
4   movie
5   movie
6   book
7   book
8   book

How do I write an update statement in SQL Server. Please help.

Answer 1

You can use a self joined table

CREATE TABLE tab1 (
  [id] VARCHAR(7),
  [value] VARCHAR(5)
);

INSERT INTO tab1
  ([id], [value])
VALUES
  ('1', 'music'),
  ('2', NULL),
  ('3', 'movie'),
  ('4', NULL),
  ('5', NULL),
  ('6', 'book'),
  ('7', NULL),
  ('8', NULL);
GO

Update a Set
    a.[value]  = b.[value] 
From
    tab1 As a    
    Cross Apply (
        Select Top 1 [value] 
        From tab1 
        Where 
            ( [id] < a.[id] ) 
            And ( a.[value]  Is Null ) 
            And ( [value]  Is Not Null ) 
        Order By [id] Desc
    ) As b
GO

5 rows affected

SELECT * FROM tab1
GO

id | value
:- | :----
1  | music
2  | music
3  | movie
4  | movie
5  | movie
6  | book 
7  | book 
8  | book 

db<>fiddle here

You can something like this

WITH atab1 AS (
SELECT [id], [value] from tab1 WHERE [id] BETWEEN 1 ANd 5)
Update a Set
    a.[value]  = b.[value] 
From
    atab1 As a    
    Cross Apply (
        Select Top 1 [value] 
        From tab1 
        Where 
            ( [id] < a.[id] ) 
            And ( a.[value]  Is Null ) 
            And ( [value]  Is Not Null ) 
        Order By [id] Desc
    ) As b
GO

3 rows affected

SELECT * FROM tab1
GO

id | value
:- | :----
1  | music
2  | music
3  | movie
4  | movie
5  | movie
6  | book 
7  | null 
8  | null 

WITH atab1 AS (
SELECT [id], [value] from tab1 WHERE [id] BETWEEN 3 ANd 8)
Update a Set
    a.[value]  = b.[value] 
From
    atab1 As a    
    Cross Apply (
        Select Top 1 [value] 
        From tab1 
        Where 
            ( [id] < a.[id] ) 
            And ( a.[value]  Is Null ) 
            And ( [value]  Is Not Null ) 
        Order By [id] Desc
    ) As b
GO

2 rows affected

SELECT * FROM tab1
GO

id | value
:- | :----
1  | music
2  | music
3  | movie
4  | movie
5  | movie
6  | book 
7  | book 
8  | book 

db<>fiddle here