Replace nulls in column with Auto Increment by number

Question

Hi I have one table with two columns. S.No and Date. S.No has null values. S.No Date(DD-MM-YYYY) Null 19-10-1993 Null 12-03-1995 Null 15-02-2017 Null 26-08-2010 Null 01-06-2016 I need to replace these nulls with number in the sequence based on date. Can you please help on this? Thanks in advance!!!

Nilesh Shah · Answer

Hi Rajeevkumar I created a table same as yours and following update query worked you will need to use a with statement: With cte As ( select SrNo ISNULL (SrNoROW_NUMBER() over( ORDER BY DateValue ASC )) as SequenceValue DateValue from testtable ) UPDATE cte SET SrNo=SequenceValue; please mark my answer as accepted if it works for you

Rajeevkumar · Answer

select ISNULL (S. No ROW_NUMBER() over( ORDER BY Date ASC )) date from table The above query is working but the column is not updating with the values.

Rafnas T P · Answer

try below select statement select ISNULL (S. No ROW_NUMBER() over( ORDER BY Date ASC )) date from table

Rajeevkumar · Answer

I need answer like this. S.No Date(DD-MM-YYYY) 1 19-10-1993 2 12-03-1995 5 15-02-2017 3 26-08-2010 4 01-06-2016

Pradeep Kumar Gupta · Answer

Rajeevkumar is there any Logic or sorting method in your result?? Or you need the maximum s.No. in mid of the result??

Sundar · Answer

Then try like this select ISNULL(S.No'') date from table

Pradeep Kumar Gupta · Answer

If we have some value in S.No. Column. And we required them is well as previous. Without DROP the column can we do it?

Sundar · Answer

I hope this will help Thanks in advance ALTER TABLE [ yourTable ] DROP COLUMN ID ALTER TABLE [ yourTable ] ADD ID INT IDENTITY ( 1  1 )

S.No	Date(DD-MM-YYYY)
Null	19-10-1993
Null	12-03-1995
Null	15-02-2017
Null	26-08-2010
Null	01-06-2016