SQL Server – How to Add a Sequence Number to Existing Rows

sequencesql serverupdate

I have a table with an OrderSequence column. The schema looks roughly like this:

id 
Name 
OrderSequence

I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the OrderSequence for sorting purposes. I don't want to loop through the rows from the application, executing 'n' SQL queries one at a time. How can I do this using a stored procedure?

Sample data

id Name OrderSequence
1  A    0
2  B    0
3  C    0

Desired result

id Name OrderSequence
1  A    1
2  B    2
3  C    3

Best Answer

You can accomplish this using ROW_NUMBER() syntax, as mentioned by @Kin :

USE Tempdb;
CREATE TABLE dbo.testRowNum
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Name VARCHAR(255)
    , SequenceNum INT NULL
);
INSERT INTO testRowNum (Name) VALUES ('A'), ('B'), ('C');

SELECT *
FROM dbo.testRowNum;

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum 
    INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM dbo.testRowNum) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

SELECT *
FROM dbo.testRowNum;

Output:

Output

If you want only update certain rows, you can use a Table Variable (or a #temp table if you are updating a LOT of rows), as such:

DECLARE @IDsToUpdate TABLE
(
    ID INT
);

INSERT INTO @IDsToUpdate VALUES (1),(3);

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum 
    INNER JOIN (
        SELECT t.ID
            , ROW_NUMBER() OVER (ORDER BY t.ID) AS RowNum 
        FROM dbo.testRowNum t
            INNER JOIN @IDsToUpdate i ON t.ID = i.ID
        ) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

The example above results in the following output:

Output

An improvement (based on a suggestion by Daniel Hutmacher) avoids the join and results in a more efficient query plan (no join or Halloween protection):

WITH Numbered AS
(
    SELECT 
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
)
UPDATE Numbered
SET SequenceNum = RowNum;

Execution plan

Or, when using the @IDsToUpdate table:

WITH Numbered AS
(
    SELECT 
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
    WHERE EXISTS
    (
        SELECT * 
        FROM @IDsToUpdate AS I
        WHERE I.ID = dbo.testRowNum.ID
    )
)
UPDATE Numbered
SET SequenceNum = RowNum;
Related Question