CODEWITHSUNDEEP
sqlsql-server-2008
gruber
gruber

Reputation: 29727

Make a field monotonic across all rows

I have table in my sql server database which I want to convert to PK column

To do that I want to change value of each row in this column to 1,2,3 ...

Could You write T-Sql query for that task ?

Thanks for help

begin state:

Id | Name |
----------
1  | One  |
2  | Two  |
2  | Three|
x  | xxx  |

result:

Id | Name |
----------
1  | One  |
2  | Two  |
3  | Three|
4  | xxx  |

Upvotes: 1

Views: 661

Answers (2)

Wired604
Wired604

Reputation: 370

you can also do it with name if you dont have the id!

;with cte as
(
SELECT Id, ROW_NUMBER() over (order by name) as rn
from YourTable
)
UPDATE cte SET Id = rn

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452978

;with cte as
(
SELECT Id, ROW_NUMBER() over (order by Id) as rn
from YourTable
)
UPDATE cte SET Id = rn

Upvotes: 4

Related Questions