SQL Server UPDATE a table by using ORDER BY

Question

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

User · Answer

You can not use ORDER BY as part of the UPDATE statement  you can use in sub-selects that are part of the update    UPDATE Test  SET Number   rowNumber  FROM Test INNER JOIN   SELECT ID  row number   OVER  ORDER BY ID DESC  as rowNumber FROM Test  drRowNumbers ON drRowNumbers ID   Test ID

User · Answer

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 · Answer

I ran into the same problem and was able to resolve it in very powerful way that allows unlimited sorting possibilities   I created a View using  saving  2 sort orders   explanation on how to do so below    After that I simply applied the update queries to the View created and it worked great   Here are the 2 queries I used on the view   1st Query   Update  MyView Set SortID 0    2nd Query   DECLARE  sortID int SET      sortID   0 UPDATE  MyView SET      sortID   sortID    sortID   1     To be able to save the sorting on the View I put TOP  into the SELECT statement  This very useful workaround allows the View results to be returned sorted as set when the View was created when the View is opened  In my case it looked like    NOTE  Using this workaround will place an big load on the server if using a large table and it is therefore recommended to include as few fields as possible in the view if working with large tables   SELECT     TOP  600000   dbo Items ID  dbo Items Code  dbo Items SortID  dbo Supplier Date   dbo Supplier Code AS Expr1 FROM         dbo Items INNER JOIN                       dbo Supplier ON dbo Items SupplierCode   dbo Supplier Code ORDER BY dbo Supplier Date  dbo Items ID DESC    Running  SQL Server 2005 on a Windows Server 2003  Additional Keywords  How to Update a SQL column with Ascending or Descending Numbers - Numeric Values   how to set order in SQL update statement   how to save order by in sql view   increment sql update   auto autoincrement sql update   create sql field with ascending numbers

User · Answer

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 · Answer

Edit  Following solution could have problems with clustered indexes involved as mentioned here  Thanks to Martin for pointing this out   The answer is kept to educate those  like me  who don t know all side-effects or ins and outs of SQL Server     Expanding on the answer gaven by Quassnoi in your link  following works   DECLARE  Test TABLE  Number INTEGER  AText VARCHAR 2   ID INTEGER  DECLARE  Number INT  INSERT INTO  Test VALUES  1   A   1  INSERT INTO  Test VALUES  2   B   2  INSERT INTO  Test VALUES  1   E   5  INSERT INTO  Test VALUES  3   C   3  INSERT INTO  Test VALUES  2   D   4   SET  Number   0      WITH q AS       SELECT  TOP 1000000       FROM     Test     ORDER BY             ID               UPDATE  q SET      Number   Number    Number   1

User · Answer

I had a similar problem and solved it using ROW NUMBER   in combination with the OVER keyword  The task was to retrospectively populate a new TicketNo  integer  field in a simple table based on the original CreatedDate  and grouped by ModuleId - so that ticket numbers started at 1 within each Module group and incremented by date  The table already had a TicketID primary key  a GUID    Here s the SQL   UPDATE Tickets SET TicketNo T2 RowNo FROM Tickets INNER JOIN     select TicketID  TicketNo        ROW NUMBER   OVER  PARTITION BY ModuleId ORDER BY DateCreated  AS RowNo from Tickets     AS T2 ON T2 TicketID   Tickets TicketID   Worked a treat

User · Answer

No   Not a documented 100  supported way  There is an approach sometimes used for calculating running totals called  quirky update  that suggests that it might update in order of clustered index if certain conditions are met but as far as I know this relies completely on empirical observation rather than any guarantee   But what version of SQL Server are you on  If SQL2005  you might be able to do something with row number and a CTE  You can update the CTE   With cte As   SELECT id Number  ROW NUMBER   OVER  ORDER BY id DESC  AS RN FROM Test   UPDATE cte SET Number RN

User · Answer

update based on Ordering by the order of values in a SQL IN   clause  Solution   DECLARE  counter int SET  counter   0   WITH q  AS           select   from Products WHERE ID in  SELECT TOP  10  ID FROM Products WHERE  ID IN  3 2 1   ORDER BY ID DESC            update q set Display   counter   counter    counter   1   This updates based on descending 3 2 1   Hope helps someone

User · Answer

The row number   function would be the best approach to this problem   UPDATE T     SET T Number   R rowNum     FROM Test T     JOIN           SELECT T2 id row number   over  order by T2 Id desc  rowNum from Test T2       R on T id R id

[sql-server] SQL Server: UPDATE a table by using ORDER BY

Examples related to sql-server