How to update and order by using ms sql
WITH q AS ( SELECT TOP 10 * FROM messages WHERE status = 0 ORDER BY priority DESC )UPDATE qSET status = 10
You can do a subquery where you first get the IDs of the top 10 ordered by priority and then update the ones that are on that sub query:
UPDATE messages SET status=10 WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);
I have to offer this as a better approach - you don't always have the luxury of an identity field:
UPDATE mSET [status]=10FROM ( Select TOP (10) * FROM messages WHERE [status]=0 ORDER BY [priority] DESC) mYou can also make the sub-query as complicated as you want - joining multiple tables, etc...
Why is this better? It does not rely on the presence of an identity field (or any other unique column) in the messages table. It can be used to update the top N rows from any table, even if that table has no unique key at all.