SQL 如何使用ms sql进行更新和排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/655010/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update and order by using ms sql
提问by Toad
Ideally I want to do this:
理想情况下，我想这样做：
UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC;
In English: I want to get the top 10 available (status=0) messages from the DB and lock them (status=10). A message with a higher priority should be gotten first.
英语：我想从数据库中获取前 10 条可用（状态 = 0）消息并锁定它们（状态 = 10）。应该首先获得具有更高优先级的消息。
unfortunately MS SQL doesn't allow an order by clause in the update.
不幸的是，MS SQL 不允许在更新中使用 order by 子句。
Anyway how to circumvent this?
无论如何如何规避这个？
采纳答案by Eduardo Crimi
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:
您可以执行一个子查询，首先获取按优先级排序的前 10 个 ID，然后更新该子查询中的 ID：
UPDATE messages
SET status=10
WHERE ID in (SELECT TOP (10) Id
FROM Table
WHERE status=0
ORDER BY priority DESC);
回答by Quassnoi
WITH q AS
(
SELECT TOP 10 *
FROM messages
WHERE status = 0
ORDER BY
priority DESC
)
UPDATE q
SET status = 10
回答by mfascino
I have to offer this as a better approach - you don't always have the luxury of an identity field:
我必须提供一个更好的方法 - 您并不总是拥有身份字段的奢侈：
UPDATE m
SET [status]=10
FROM (
Select TOP (10) *
FROM messages
WHERE [status]=0
ORDER BY [priority] DESC
) m
You 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 messagestable. It can be used to update the top N rows from any table, even if that table has no unique key at all.
为什么这样更好？它不依赖于表中标识字段（或任何其他唯一列）的存在messages。它可用于更新任何表的前 N 行，即使该表根本没有唯一键。
回答by dotjoe
UPDATE messages SET
status=10
WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);
回答by Jhonny D. Cano -Leftware-
As stated in comments below, you can use also the SET ROWCOUNT clause, but just for SQL Server 2014 and older.
正如下面的评论中所述，您也可以使用 SET ROWCOUNT 子句，但仅适用于 SQL Server 2014 及更早版本。
SET ROWCOUNT 10
UPDATE messages
SET status = 10
WHERE status = 0
SET ROWCOUNT 0
More info: http://msdn.microsoft.com/en-us/library/ms188774.aspx
更多信息：http: //msdn.microsoft.com/en-us/library/ms188774.aspx
Or with a temp table
或使用临时表
DECLARE @t TABLE (id INT)
INSERT @t (id)
SELECT TOP 10 id
FROM messages
WHERE status = 0
ORDER BY priority DESC
UPDATE messages
SET status = 10
WHERE id IN (SELECT id FROM @t)
