0

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.

New contributor
Satya Gurucharan is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

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

3
  • Thankyou this works very fast for a small data set , but the thing is I am trying to update 40 million records and it is taking long time to update the records. – Satya Gurucharan Jun 9 at 22:28
  • there are some approaches, but this is the fastest i know. and it is used in many databases with millions of rows. – nbk Jun 10 at 7:30
  • ok Thankyou for all the help... appreciate it. – Satya Gurucharan Jun 10 at 15:14

Your Answer

Satya Gurucharan is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.