SQL UPDATE TOP with ORDER BY?

by

you can use common table expression for this:

;with cte as (
   select top (@MaxRecords)
       status
   from Messages 
   where Status="N" and InsertDate >= getdate()
   order by ...
)
update cte set
    status="P"
output inserted.*

This one uses the fact that in SQL Server it’s possible to update cte, like updatable view.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. How to select just the row in the second table?
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  6. sql query to return differences between two tables
  7. Generate SQL Create Scripts for existing tables with Query
  8. SQL Server 2005 How Create a Unique Constraint?
  9. SQL Server: Make all UPPER case to Proper Case/Title Case
  10. How do I delete from multiple tables using INNER JOIN in SQL server
  11. Does Foreign Key improve query performance?
  12. Export SQL query data to Excel
  13. What is a ‘multi-part identifier’ and why can’t it be bound?
  14. SQL Server convert string to datetime
  15. sql server select first row from a group
  16. CASE WHEN statement for ORDER BY clause
  17. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  18. CTE error: “Types don’t match between the anchor and the recursive part”
  19. Automate Version number Retrieval from .Dtsx files
  20. TransactSQL to run another TransactSQL script
  21. How do I remove extended ASCII characters from a string in T-SQL?
  22. SQL server identity column values start at 0 instead of 1
  23. The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
  24. How can I see all the “special” characters permissible in a varchar or char field in SQL Server? [closed]
  25. The most elegant way to generate permutations in SQL server
  26. Comma Separated values with SQL Query
  27. Using SQL Server as a DB queue with multiple clients
  28. Select SQL Server database size
  29. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  30. MAX vs Top 1 – which is better?

Leave a Comment