SQL Server: UPDATE a table by using ORDER BY

user_414952 0
Viewed 172283

I would like to know if there is a way to use an order by clause when updating a table. I am updating a table and setting a consecutive number, that's why the order of the update is important. Using the following sql statement, I was able to solve it without using a cursor:

DECLARE @Number INT = 0

UPDATE Test
SET @Number = Number = @Number +1

now what I'd like to to do is an order by clause like so:

 DECLARE @Number INT = 0

 UPDATE Test
 SET @Number = Number = @Number +1
 ORDER BY Test.Id DESC

I've read: How to update and order by using ms sql The solutions to this question do not solve the ordering problem - they just filter the items on which the update is applied.

Take care, Martin

sql-server
9 Answers
SET @pos := 0;
UPDATE TABLE_NAME SET Roll_No = ( SELECT @pos := @pos + 1 ) ORDER BY First_Name ASC;

In the above example query simply update the student Roll_No column depending on the student Frist_Name column. From 1 to No_of_records in the table. I hope it's clear now.

user_51570320 
IF OBJECT_ID('tempdb..#TAB') IS NOT NULL
BEGIN
    DROP TABLE #TAB
END

CREATE TABLE #TAB(CH1 INT,CH2 INT,CH3 INT)

DECLARE @CH2 INT = NULL , @CH3 INT=NULL,@SPID INT=NULL,@SQL NVARCHAR(4000)='', @ParmDefinition NVARCHAR(50)= '',
@RET_MESSAGE AS VARCHAR(8000)='',@RET_ERROR INT=0


SET @ParmDefinition='@SPID INT,@CH2 INT OUTPUT,@CH3 INT OUTPUT'

SET @SQL='UPDATE T
            SET CH1=@SPID,@CH2= T.CH2,@CH3= T.CH3
            FROM #TAB T WITH(ROWLOCK)
            INNER JOIN (
                        SELECT TOP(1)  CH1,CH2,CH3
                        FROM
                        #TAB WITH(NOLOCK)
                        WHERE CH1 IS NULL
                        ORDER BY CH2 DESC) V ON T.CH2= V.CH2 AND T.CH3= V.CH3' 

INSERT INTO #TAB
(CH2 ,CH3 )
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4

BEGIN TRY
    WHILE EXISTS(SELECT TOP 1 1 FROM #TAB WHERE CH1 IS NULL)
    BEGIN

        EXECUTE @RET_ERROR = sp_executesql @SQL, @ParmDefinition,@SPID =@@SPID,  @CH2=@CH2 OUTPUT,@CH3=@CH3 OUTPUT;  

        SELECT * FROM #TAB
        SELECT @CH2,@CH3

    END

END TRY
BEGIN CATCH

    SET @RET_ERROR=ERROR_NUMBER()
    SET @RET_MESSAGE =  '@ERROR_NUMBER : ' + CAST(ERROR_NUMBER()  AS VARCHAR(255)) + '@ERROR_SEVERITY  :' + CAST( ERROR_SEVERITY()  AS VARCHAR(255)) 
    + '@ERROR_STATE :' + CAST(ERROR_STATE() AS VARCHAR(255)) + '@ERROR_LINE :' + CAST( ERROR_LINE() AS VARCHAR(255)) 
    +  '@ERROR_MESSAGE :' + ERROR_MESSAGE()  ;

    SELECT @RET_ERROR,@RET_MESSAGE;

END CATCH
user_137975420
Related