Reset or Update Row Position Integer in Database Table

I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this “Order Rank Integer”.

Display queries for items always append a “ORDER BY OrderRankInt” when retrieving data so the user sees the items in the order they previously specified.

The problem is that this integer column collects a lot of duplicate values after the table items are re-ordered a bit. Hence…

Table
--------
Name | OrderRankInt
a    | 1
b    | 2
c    | 3
d    | 4
e    | 5
f    | 6

After a lot of reordering by the user becomes….

Table
--------
Name | OrderRankInt
a    | 1
b    | 2
c    | 2
d    | 2
e    | 2
f    | 6

These duplicates are primarily because of insertions and user specified order numbers. We’re not trying to prevent duplicate order ranks, but we’d like a way to ‘Fix’ the table on item inserts/modifies.

Is there a way I can reset the OrderRankInt column with a single UPDATE Query? Or do I need to use a cursor? What would the syntax for that cursor look like?

Thanks, Kervin

EDIT

Update with Remus Rusanu solution. Thanks!!

CREATE PROCEDURE EPC_FixTableOrder
@sectionId int = 0
AS
BEGIN

-- "Common Table Expression" to append a 'Row Number' to the table
WITH tempTable AS 
(
    SELECT OrderRankInt, ROW_NUMBER() OVER (ORDER BY OrderRankInt) AS rn
    FROM dbo.[Table]
    WHERE sectionId = @sectionId -- Fix for a specified section
)
UPDATE tempTable        
SET OrderRankInt = rn;  -- Set the Order number to the row number via CTE

END
GO

Answer

with cte as (
 select OrderId, row_number() over (order by Name) as rn
 from Table)
update cte
 set OrderId = rn;

This doesn’t account for any foreign key relationships, I hope you are taken care of those.

Advertisement

Answer