Re-Sequence out of order numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Re-Sequence out of order numbers

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2012-09-08 : 00:38:51

Hi,

I have the following data in a table:

Account SEQ
12345 1
12345 2
12345 4
12345 5
12345 7

I need to fix the SEQ field so that no gaps exist, like this:

Account SEQ
12345 1
12345 2
12345 3
12345 4
12345 5

Is there a way to do this with T-SQL?

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-08 : 06:59:54

In SQL 2005 or later, you can use the row_number function to do this. In the query below, if you remove the UPDATE statement and uncomment the line above that, you can see what the updates are going to be. If that looks right, run the original code.

;WITH cte AS
(
	SELECT SEQ,ROW_NUMBER() OVER (ORDER BY SEQ) AS NewSEQ
	FROM YourTable
)
-- SELECT * FROM cte;
UPDATE cte SET SEQ = NewSEQ;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources