SQL Server: UPDATE a table by using ORDER BY

by

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

More Related Contents:

  1. Local and global temporary tables in SQL Server
  2. Get first day of week in SQL Server
  3. What is the significance of 1/1/1753 in SQL Server?
  4. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  5. SQL Server add auto increment primary key to existing table
  6. How to delete large data of table in SQL without log?
  7. Insert Update trigger how to determine if insert or update
  8. Change Notification with Sql Server 2008
  9. How do you create a yes/no boolean field in SQL server?
  10. Connecting to MS SQL Server with Windows Authentication using Python?
  11. Using COALESCE function to make values separated with commas
  12. How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?
  13. How to find the port for MS SQL Server 2008?
  14. Assign result of dynamic sql to variable
  15. How do I create a multiple-table check constraint?
  16. Stored Procedure and Permissions – Is EXECUTE enough?
  17. Best way to store time (hh:mm) in a database
  18. Stored procedure – return identity as output parameter or scalar
  19. SQL Server Group by Count of DateTime Per Hour?
  20. How to import a DBF file in SQL Server
  21. SQL Server Dependencies
  22. Why does a parameterized query produces vastly slower query plan vs non-parameterized query
  23. SQL Server: Filter output of sp_who2
  24. How can I insert binary file data into a binary SQL field using a simple insert statement?
  25. Why do I get the error “Xml data type is not supported in distributed queries” when querying a linked server for non-xml data?
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. Is there an “IF EXISTS” test for a linked server?
  28. Unique identifier (guid) as primary key in database design
  29. How to extract this specific substring in SQL Server?
  30. SQL Server stored procedure return code oddity

Leave a Comment