如何更新TOP 400？

发布于 2024-08-03 21:16:03 字数 148 浏览 2 评论 0 原文

我想更新数据库表中的前 400 行。伪SQL如下，我该怎么做？

UPDATE top (400) db.dbo.tbl
SET column1 = 2
WHERE column2 = 1
  AND column1 is null

原文

I would like to update the top 400 rows in a database table. The pseudo SQL is below, how can I do this?

UPDATE top (400) db.dbo.tbl
SET column1 = 2
WHERE column2 = 1
  AND column1 is null

分享到QQ

分享到微博

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

发布评论

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

丢了幸福的猪 2024-08-10 21:16:03

 UPDATE db.dbo.tbl SET column1 = 2 WHERE
 primaryID IN (
   SELECT TOP (400) primarkyID FROM db.dbo.tbl
   WHERE column2 = 1 AND column1 IS NULL
 )

但我不喜欢这样，因为无法保证哪个是前 400 名，您可能需要添加一些其他类型的标准。甚至还有子查询的 Order By。

 UPDATE db.dbo.tbl SET column1 = 2 WHERE
 primaryID IN (
   SELECT TOP (400) primarkyID FROM db.dbo.tbl
   WHERE column2 = 1 AND column1 IS NULL
 )

But I don't like this as there's no way to guarantee WHICH top 400, you might want to add some other type of criteria. And even an Order By to the subquery.

回复收藏 0 原文

蓝戈者 2024-08-10 21:16:03

WITH    q AS
        (
        SELECT  TOP 400 *
        FROM    db.dbo.tb
        WHERE   column2 = 1
                AND column1 is null
        ORDER BY
                column3 -- choose your order!
        )
UPDATE  q
SET     column2 = 2

WITH    q AS
        (
        SELECT  TOP 400 *
        FROM    db.dbo.tb
        WHERE   column2 = 1
                AND column1 is null
        ORDER BY
                column3 -- choose your order!
        )
UPDATE  q
SET     column2 = 2

回复收藏 0 原文

我爱人 2024-08-10 21:16:03

如何确定前 400 名？如果没有 order by，就无法保证始终选择同一组，因此可能会更新错误的记录。

回复收藏 0 原文

箹锭⒈辈孓 2024-08-10 21:16:03

您可能正在寻找类似这样的内容：

update db.dbo.tbl set column1 = 2 
where ID in (
  select top 400 ID from db.dbo.tbl
  where column2 = 1 and column1 is null --the criteria have been moved here
  order by ID --order by clause recommended
  )

其中 ID 是表的主键列。

You're probably looking for something like this:

update db.dbo.tbl set column1 = 2 
where ID in (
  select top 400 ID from db.dbo.tbl
  where column2 = 1 and column1 is null --the criteria have been moved here
  order by ID --order by clause recommended
  )

where ID is the primary key column of the table.

回复收藏 0 原文