Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
Suggest you to look into ROW_NUMBER(). Another approach will be to use Number Table. Also, updating in batches will help to avoid bloating of T-log file. –  Kin Jul 30 at 2:50
    
I don't really have to use row_number() as i'm only planning to have a increment number that starts from 0, i'm a novice with sql, that table is actually a child table with maybe hundreds of thousands of records, i already captured the ids of rows to be updated. I need help to set the OrderSequence from 0 to N –  Lee Gary Jul 30 at 3:15
    
Without looping from code and firing N number of sql statements that is –  Lee Gary Jul 30 at 3:15

2 Answers 2

up vote 6 down vote accepted

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;
share|improve this answer
    
what if i have a,b,c,d,e,f and i have id 1,2,3 and i only want to update 1 - 3? –  Lee Gary Jul 30 at 5:32
    
i have modified it slightly to be something like this 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 where ID in (1,2,3)) x ON testRowNum.ID = x.ID WHERE dbo.testRowNum.ID in (1, 2, 3);, is it necessary to pass in the IDs twice? –  Lee Gary Jul 30 at 5:38
    
I've updated my answer to show one way of accomplishing a selective update. –  Max Vernon Jul 30 at 16:00
1  
It was Daniel's. I just made it look pretty and did the admin :) –  Paul White Aug 1 at 21:25
1  
Man, if only i have dbas like you guys –  Lee Gary Aug 4 at 1:20

Define your stored procedure to take a table valued parameter (TVP). The TVP will have two columns, one for ID and one for OrderSequence. Populate this TVP in your application, setting just the IDs you wish to amend. The SP can perform an UPDATE by joining to the TVP. This is more efficient than a cursor or multiple SQL calls.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.