How to update a column in the auto increment database and taking into account a sort

Asked

Viewed 54 times

1

I need to update a database table by changing a column with the auto increment value but I must take into account a sort, I managed to update with the following code with auto increment but not able to do taking into account the ordering, the sorting field is in the same update table with the name Description.

  DECLARE @count int
  SET @count = 0
  update MinhaTabela set Codigo = @count, @count=@count+1
sql sql-server

1 answer

1


Since you cannot use the ORDER BY clause in an update, you can for example perform the update indirectly via a CTE. 

;WITH CTE As
(
  SELECT Codigo, ROW_NUMBER() OVER (ORDER BY Descricao) AS RN
    FROM MinhaTabela
)
UPDATE CTE
   SET Codigo = RN

If you want the Code to start at 0 (Zero), just replace 1 to the RN:

;WITH CTE As
(
  SELECT Codigo, ROW_NUMBER() OVER (ORDER BY Descricao) AS RN
    FROM MinhaTabela
)
UPDATE CTE
   SET Codigo = RN - 1
  • 1

    Very good worked out I did not know CTE I have little knowledge with MSSQL, I’ll give a studied up to understand better. OBG

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.