This my first time when I use SQL and I need your help. I have got a table like this:

And I can order it by date, then I have got this:

I have done this with function UPDATE and ORDER BY. There is a question now. How to order by date, and change the value of ID with update function to get the result like this:

Is there any quick method to sort by date and leave the order of ID? I want to help my friend with that but I have never been working with SQL. Sorry for my english.

CodePudding user response：

Try following code

UPDATE table t
SET t.id = t1.NEW_ID
FROM (
      SELECT ROW_NUMBER() OVER (ORDER BY [Date]) AS New_ID
      FROM TABLE
 ) t1

CodePudding user response：

Please try this,

with my_cte as (
select id, date
, row_number() over(order by date asc) rn 
from test
)
update test set id = 
(select  min(rn) from my_cte 
where test.id = my_cte.id and test.date=my_cte.date);