Renumbering values in a numeric column 1

[larrydavid]

Hello, I have a challenge I'm hoping someone can help me with. I have a SQL Server 2005 table with a (non-PK) int column which is currently ordered like this:

1
2
4
5
6
8
9

What I need to do is remove the gaps (missing numbers) and reorder the values like so:

1
2
3
4
5
6
7

Is there a stored procedure or way to simply do this in an update statement? This would be a one-time update.

Thank you,
Lawrence

 

[markros]

SQL Server 2005 and up:

;with ReNumbered as (select ID, Field, row_number() over (order by Field) as NewField from MyTable)

update ReNumbered set Field = NewField

If it's SQL 2000.

select *, identity (int,1,1) as NewField into #Temp 
from myTable
order by Field

update myTable set Field = T.NewField
from myTable inner join #Temp T on myTable.PK = #Temp.PK

PluralSight Learning Library

 

[larrydavid]

Markros,

Wow, thank you so much for the quick reply. You really know your stuff. Much appreciated.

Lawrence