如何使用 SqlServer2008从表中删除前1000行？

我在 SQLServer 中有一个表。我想从中删除前1000行。但是，我试过这样做，但是我没有只删除前1000行，而是删除了表中的所有行。

密码如下:

delete from [mytab]
select top 1000
a1,a2,a3
from [mytab]
318417 次浏览
小开 
delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)
小开

使用 sql2005 + 可能更好:

DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions

对于 Sql2000:

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

但是

如果希望删除行的 具体点子集而不是任意子集，则应显式指定子查询的顺序:

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

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

小开
最佳答案

您尝试的代码实际上是两个语句: DELETE后面跟着 SELECT。

你不能定义 TOP是按什么顺序排列的。

对于特定的排序标准，从 慢性创伤性脑病或类似的表表达式中删除是最有效的方法。

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


DELETE FROM [MyTable] WHERE .....
小开

如下面的链接所定义的，您可以直接删除

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

Http://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx

小开

很快的，试试看:

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

用表名替换 YourTABLE, 按一个数字，例如1000, pk是表的主键字段的名称。

小开

我同意 Hamed Elahi和 葛罗芬戴尔。

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

/*
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

提交答案