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

发布于 2024-07-15 09:18:28 字数 261 浏览 3 评论 0原文

理想情况下，我想这样做：

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

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

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

无论如何如何规避这个？

原文

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.

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

Anyway how to circumvent this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

输什么也不输骨气 2024-07-22 09:18:28

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

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

回复收藏 0 原文

缺⑴份安定 2024-07-22 09:18:28

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

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

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

回复收藏 0 原文

人间☆小暴躁 2024-07-22 09:18:28

我必须将其作为一种更好的方法提供 - 您并不总是拥有身份字段的奢侈：

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

您还可以根据需要使子查询变得复杂 - 连接多个表等...

为什么这更好？它不依赖于 messages 表中是否存在标识字段（或任何其他唯一列）。它可用于更新任何表中的前 N 行，即使该表根本没有唯一键。

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 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.

回复收藏 0 原文

梦旅人picnic 2024-07-22 09:18:28

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

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

回复收藏 0 原文

追星践月 2024-07-22 09:18:28

正如下面的注释中所述，您还可以使用 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)

As stated in comments below, you can use also the SET ROWCOUNT clause, but just for SQL Server 2014 and older.

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

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)

回复收藏 0 原文

~没有更多了~