如何使用 ms sql 进行更新和排序

2022-01-17 00:00:00 sql-update sql tsql sql-server sql-order-by

理想情况下我想这样做:

Ideally I want to do this:

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

英文:我想从数据库中获取前 10 条可用 (status=0) 消息并锁定它们 (status=10).优先级更高的消息应该首先得到.

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.

不幸的是 MS SQL 不允许在更新中使用 order by 子句.

unfortunately MS SQL doesn't allow an order by clause in the update.

无论如何如何规避这个?

Anyway how to circumvent this?

推荐答案

您可以进行子查询，首先获取按优先级排序的前 10 名的 ID，然后更新该子查询中的 ID:

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);

相关文章