如何使用MS SQL进行更新和排序

Question

理想情况下，我想做到这一点：

UPDATE TOP (10) messages SET status=10 WHERE status=0 ORDER BY priority DESC;

我想从数据库中获取前10条状态为0的可用消息并将它们锁定（状态为10）。优先级较高的消息应首先获取。

不幸的是，MS SQL不允许在update语句中使用order by子句。

那么有什么方法可以绕过这个限制呢？

- reinier

Answer 1

;WITH    q AS
        (
        SELECT  TOP 10 *
        FROM    messages
        WHERE   status = 0
        ORDER BY
                priority DESC
        )
UPDATE  q
SET     status = 10

- Quassnoi

---

+1 对于CTE来说非常好，这种方式在使用OUTPUT子句时可以省去一个JOIN。 - nitzmahone

这实际上是更理想的答案，而不是被接受的答案。 - Carvellis

@Haroon：哪两个语句？ - Quassnoi

@Quassnoi，我认为Haroon的意思是，为了保证原子性，您需要使用事务来包装CTE和更新操作。 - xkingpin

我必须使用前500000行来更新一个临时表中的所有行，该表不超过1000行，以便使用order by。 - Jules

在使用WITH语句时，一定要在前面加上分号;，否则会出现这个错误。希望能对大家有所帮助。 - Shaiju T

Answer 2

您可以执行一个子查询，首先获取按优先级排序的前10个ID，然后更新位于该子查询中的记录：

UPDATE  messages 
SET status=10 
WHERE ID in (SELECT TOP (10) Id 
             FROM Table 
             WHERE status=0 
             ORDER BY priority DESC);

- Quaky

---

我想为这个解决方案创建一个索引。那么我应该使用：(priority desc, status) 还是 (status, priority desc)？换句话说，order by 是在 where 之前使用吗？ - Toad

实际上查询语句不正确...除非同时指定 TOP，否则无法在子查询中使用 ORDER BY 子句。（这是 SQL 引擎的说法）。 因此，正确的查询应该是： UPDATE messages SET status=10 WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC); - Toad

你说得对，我在最后的编辑时漏掉了顶部。我会按照你说的进行编辑。 - Eduardo Crimi

Answer 3

我可以提供一个更好的方法 - 您并不总是拥有标识字段的豪华条件：

UPDATE m
SET [status]=10
FROM (
  Select TOP (10) *
  FROM messages
  WHERE [status]=0
  ORDER BY [priority] DESC
) m

你可以把子查询设计得越复杂越好，比如加入多张表等等...

为什么这样更好？它不依赖于在 messages 表中存在自增字段（或任何其他唯一列）。它可以用于更新任何表中的前 N 行，甚至该表根本没有唯一键。

- mfascino

---

这个答案与dotjoe的答案有何不同：https://dev59.com/5HRB5IYBdhLWcg3wV156#655561 - Athafoud

如果你不花时间去理解我的回答，我对于被踩也不感到惊讶。我的回答与dotjoe和其他大多数回答不同，因为其他回答假设表格有一个标识字段（...where ID IN...）。但你不能总是假设表格会有标识字段。我提供了一种在表格中没有ID字段时的替代方案。请在踩之前先尝试理解我的回答。 - mfascino

感谢您的简要解释。请花些时间编辑您的答案，并添加刚才提到的细节。这将使您的答案更完整，更易于理解，而不需要花费太多时间。一旦您编辑了您的答案，我将撤销我的投票。 - Athafoud

@Athafoud，我稍微扩展了一下答案（虽然我不是原帖作者），你能否请审核一下？ - Ed Avis

从所有其他选项中，这个选项对我来说具有最大的灵活性。感谢您发布这个解决方案。 - Dale Moore

Answer 4

UPDATE messages SET 
 status=10 
WHERE ID in (SELECT TOP (10) Id FROM Table WHERE status=0 ORDER BY priority DESC);

- dotjoe

Answer 5

如下评论所述，您也可以使用SET ROWCOUNT子句，但仅适用于SQL Server 2014及更早版本。

SET ROWCOUNT 10

UPDATE messages
SET status = 10 
WHERE status = 0 

SET ROWCOUNT 0

更多信息：http://msdn.microsoft.com/en-us/library/ms188774.aspx 或者使用临时表。

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)

- Jhonny D. Cano -Leftware-

---

对于那些一直读到这里的人（你永远不知道）， SET ROWCOUNT 面临废弃 http://msdn.microsoft.com/en-us/library/ms188774.aspx “在将来的 SQL Server 版本中，使用 SET ROWCOUNT 将不会影响 DELETE、INSERT 和 UPDATE 语句。”- 不过至少在 SQL Server 2014 中还是可以用的。 - Tabloo Quijico

虽然 rowcount 在需要任意10行的情况下很有效，但您无法指定 order by 来确定确切的10行。 您使用临时表的示例可以工作，但它依赖于一个id列。 - Ed Avis