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 · Accepted 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 (SrNo,ROW_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.

Code Alone · Answer

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

Rafnas T P · Answer

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

Sundaram Subramanian · Answer

Then try like this    select ISNULL(S.No,''), 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

Code Alone · 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?

Sundaram Subramanian · 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