SQL Server reset sequence column-CodePudding

I have the following table and I would like to reset the Code column sequence. The Code column is just an INT column.

Current

ID | Code
1  |    1
2  |    2
3  |    6
4  |   10
5  |   12

Should be

ID | Code
1  |    1
2  |    2
3  |    3
4  |    4
5  |    5

CodePudding user response：

You can update from a CTE (Common Table Expression) with a ROW_NUMBER

WITH CTE AS (
  SELECT ID, Code
  , ROW_NUMBER() OVER (ORDER BY ID) AS rn
  FROM Your_Table
) 
UPDATE CTE
SET Code = rn;

CodePudding user response：

You could just run an update using the window function as below

update t 
set code = t1.rownum
from 
[yourtable] t 
inner join 
(select id,row_number()over(order by id) as rownum
from [yourtable])t1
on t.id = t1.id

CodePudding user response：

UPDATE table SET Code = ID WHERE 1
// you can filter with Where

Im wrong?