如何使用Sql Server 2008从数据表中删除前1000行？

Question

如何使用Sql Server 2008从数据表中删除前1000行？

147

我在 SQL Server 中有一个表。我想从中删除前1000行。然而，我尝试了这个代码，但它不仅删除了前1000行，还删除了表中的所有行。

下面是代码:

delete from [mytab] 
select top 1000 
a1,a2,a3
from [mytab]

- edgarmtze

8

需要使用ORDER BY才能使TOP有意义：请参考@Martin Smith的回答，这是五个回答中唯一一个有ORDER BY的回答。有时我感到绝望。 - gbn

2

您想删除任意1000行吗？随机选择的吗？还是例如最旧的1000行？ - Nick Chammas

15

因为delete from [mytab]是一个语句，而select top ...是另一个语句，所以您删除了整个表。 - Nick Chammas

2

你不需要为了使用 TOP 而排序，这取决于你使用 TOP 的原因。如果你需要删除 1000 万行数据并且有 1 GB 的日志空间可用，可以使用 Delete TOP(10000) From dbo.myTable（带上你的 select 子句），并一直运行它，直到没有更多的行需要删除。谁在乎它是否是任意的呢？排序只会减慢查询速度。 - tvanharp

1

我意识到这是一个古老的问题（在SO年代），但我认为人们应该考虑@gbn的评论非常重要。虽然他的评论不适用于我的情况（尝试删除记录块而不会导致LOCK问题，但并不真正关心它们被删除的顺序），但它们很可能适用于您的情况。在盲目使用不包括ORDER BY子句的答案之前，请确保考虑它们。 - Andrew Steitz

你还应该了解SQL锁升级，因为在生产系统中一次删除超过5000行数据是有原因的。https://technet.microsoft.com/zh-cn/library/ms184286(v=sql.105).aspx - Chris Ward

9个回答

129

对于 SQL2005+ 可能更好的做法是使用：

DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions

对于 Sql2000：

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
)

BUT

如果您想要删除一个特定的子集，而不是任意的子集，您应该明确指定子查询的顺序：

DELETE FROM [MyTab]
WHERE YourIdField IN 
(
  SELECT TOP 1000 
    YourIdField 
  FROM [MyTab]
  WHERE YourConditions
  ORDER BY ExplicitSortOrder
)

感谢 @gbn 提出并要求更清晰、更准确的答案。

- Oleg Dok

3

也许对你来说没什么用，但这正是问题所问的内容。 - Joachim Isaksson

1

@Joachim Isaksson：去了解一下TOP，然后再回来。在集合中，没有ORDER BY就没有TOP这样的东西。或者，去找一个能证明我错的规范参考...为了节省你的搜索时间，可以看看这个网址：http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx和http://blogs.technet.com/b/wardpond/archive/2007/07/19/database-programming-top-without-order-by.aspx - gbn

1

@gbn 你有提到子查询中的 WHERE 吗？我在选择的条件内过滤了1000个任意行，然后删除它们。这是一个有效的方案吗？是的。如果我添加 ORDER BY NEWID() 或其他内容，它不会改变什么 - 我仍然会删除由选择的条件筛选出的1000行。 - Oleg Dok

1

@gbn 问题是：我有一个 SQL 表，我想从中删除 1000 行。我尝试了这个方法，但我把整个表都删掉了。你在问题中看到了“top”这个词吗？如果没有提到，那么我们需要从 smth 中删除 top X。所以我们假设应该删除 1000 行任意行。听起来很奇怪 - 同意，但是这是一个有效的场景吗？-也同意。 - Oleg Dok

11

如果你正在寻找一个在没有ORDER BY的情况下合法使用TOP的例子：我来到这里是因为我需要删除所有符合某些条件的行，但出于性能方面的考虑，我不希望一次性删除超过10,000行。我不关心它删除哪些行，因为我会在一定时间间隔内再次运行该命令，直到所有这样的行都被删除。 - Richiban

显示剩余4条评论

32

如下链接所定义，您可以以直接的方式进行删除。

USE AdventureWorks2008R2;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

http://technet.microsoft.com/zh-cn/library/ms175486(v=sql.105).aspx

- Rishi Bhatnagar

8

delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)

- Jason Dam

4

无用提示：没有ORDER BY的TOP语句将返回任意行。 - gbn

4

也许这对你来说没有用，但这确实是问题所询问的。 - Joachim Isaksson

3

@gbn，我没有声称有任何默认排序顺序或查询在任何方面都有用，我只是提醒你问题并未要求排序，那你会建议按什么排序呢？ - Joachim Isaksson

2

@gbn，我不知道为什么你对每个人都这么敌对，只是因为这是一个起点。我并不声称我的答案是最终的解决方案，它只是一个帮助别人的建议。我认为重要的是这里从子查询返回的键。 - Jason Dam

2

这可能是提问者所寻找的全部内容。我只想为其他读者添加一条注释，强调这样一个语句删除的行不保证按任何顺序排列。 - Nick Chammas

显示剩余4条评论

4

SET ROWCOUNT 1000;

DELETE FROM [MyTable] WHERE .....

- Joe Bourne

2

当只处理1000行时，这真的很重要吗？如果是1亿行，那么你的观点可能是正确的，但对于只有1000行的情况，这是迄今为止针对SQL 2008提出的最简单的解决方案。 - Joe Bourne

3

它速度快。来试试：

DELETE FROM YourTABLE
FROM (SELECT TOP XX PK FROM YourTABLE) tbl
WHERE YourTABLE.PK = tbl.PK

将YourTABLE替换为表名， XX替换为数字，例如1000， pk是您的表的主键字段的名称。

- Hamed elahi

你实际上是从一个表中创建了两个表，然后删除了连接的部分。当你想要从一个表中删除最旧（或最新）的记录时，这种方法非常有效，因为你可以先按升序排序。这个T-SQL被微软接受了（而且很快）。 - Tequila

1

我同意 Hamed elahi 和 Glorfindel 的观点。

我的建议是您可以使用别名删除和更新

/* 
  given a table bi_customer_actions
  with a field bca_delete_flag of tinyint or bit
    and a field bca_add_date of datetime

  note: the *if 1=1* structure allows me to fold them and turn them on and off
 */
declare
        @Nrows int = 1000

if 1=1 /* testing the inner select */
begin
  select top (@Nrows) * 
    from bi_customer_actions
    where bca_delete_flag = 1
    order by bca_add_date
end

if 1=1 /* delete or update or select */
begin
  --select bca.*
  --update bca  set bca_delete_flag = 0
  delete bca
    from (
      select top (@Nrows) * 
        from bi_customer_actions
        where bca_delete_flag = 1
        order by bca_add_date
    ) as bca
end

- yeOldeDataSmythe

0

为了增强已接受的答案代码，请查看我的代码。代码将从表中每次删除1000行，然后在每次迭代后将批处理大小增加10％。这样，随着表变小，代码将删除更多的行并减少所需的循环次数。变量@count仍将存储每个删除语句影响的行数，并且当其变为零时，循环将停止。

DECLARE @batch_size INT = 10000
DECLARE @count INT = 1
WHILE @count > 0
BEGIN
    ;WITH CTE AS
    (
    SELECT TOP (@batch_size) *
    FROM [mytab]
    ORDER BY a1
    )
    DELETE FROM CTE

    SET @count = @@ROWCOUNT

    -- Increase the batch size by 10% after each iteration
    SET @batch_size = @batch_size * 1.1
END

- Joseph Wambura

0

不需要创建公用表达式（CTE）来获取和删除，因为它会首先将记录加载到内存中，然后执行删除操作，这可能会影响性能。

你只需要根据条件简单地删除前n条记录，从而快速有效地从表中删除记录，适用于非分区表。

delete top(1000)
from [mytab] 
where 
 condition[1]
 condition[2]
 .
 .
 condition[n]

- Abdul Nasir Khayam

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

249

你尝试的代码实际上是两个语句。首先是一个 DELETE，然后是一个 SELECT。

你没有指定按照什么顺序定义 TOP。

为了特定的排序标准，从一个 CTE 或类似的表达式中删除数据是最有效的方式。

;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE

- Martin Smith

20

对于那些想知道为什么不能使用“DELETE TOP (1000) FROM table ORDER BY column”语句的人，请阅读此链接（http://msdn.microsoft.com/en-us/library/ms189463.aspx）：“在使用INSERT、UPDATE、MERGE或DELETE时，TOP表达式引用的行不以任何顺序排列。” - Nick Chammas

3

@Magnus 是的，不过不是2000。可能可以在2000中使用一个派生表。我没有可用于测试的实例。 - Martin Smith

6

在未来版本的SQL Server中，使用SET ROWCOUNT不会影响DELETE、INSERT和UPDATE语句。在新的开发工作中避免在DELETE、INSERT和UPDATE语句中使用SET ROWCOUNT，并计划修改当前使用它的应用程序。 - Martin Smith

2

我用了稍微不同的方法（虽然我认为使用CTE可能更好看）： DELETE T1 FROM (SELECT TOP 1000 * FROM [MYTAB] ORDER BY A1) T1; - Abacus

5

@Liam - 如果在公共表达式（CTE）之前有任何语句，那么这些语句需要以分号结束，因此将其附加到“WITH”的前面可以预防那些没有这样做的人的抱怨。 - Martin Smith

显示剩余4条评论