如何使用 Sql Server 2008 从表中删除前 1000 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8955897/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete the top 1000 rows from a table using Sql Server 2008?
提问by cMinor
I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I instead of just deleting the top 1000 rows it deleted all the rows in the table.
我在 SQL Server 中有一个表。我想从中删除前 1000 行。但是，我尝试了此操作，但我不仅删除了前 1000 行，还删除了表中的所有行。
Here is the code:
这是代码：
delete from [mytab]
select top 1000
a1,a2,a3
from [mytab]
回答by Martin Smith
The code you tried is in fact two statements. A DELETEfollowed by a SELECT.
您尝试的代码实际上是两个语句。ADELETE后跟一个SELECT。
You don't define TOPas ordered by what.
您没有定义TOP为按什么排序。
For a specific ordering criteria deleting from a CTEor similar table expression is the most efficient way.
对于特定的排序标准，从CTE或类似的表表达式中删除是最有效的方法。
;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE
回答by Oleg Dok
May be better for sql2005+ to use:
可能更适合 sql2005+ 使用：
DELETE TOP (1000)
FROM [MyTab]
WHERE YourConditions
For Sql2000:
对于 Sql2000：
DELETE FROM [MyTab]
WHERE YourIdField IN
(
SELECT TOP 1000
YourIdField
FROM [MyTab]
WHERE YourConditions
)
BUT
但
If you want to delete specificsubset of rows instead of arbitrary subset, you should explicitly specify order to subquery:
如果要删除行的特定子集而不是任意子集，则应明确指定子查询的顺序：
DELETE FROM [MyTab]
WHERE YourIdField IN
(
SELECT TOP 1000
YourIdField
FROM [MyTab]
WHERE YourConditions
ORDER BY ExplicitSortOrder
)
Thanks tp @gbn for mentioning and demanding the more clear and exact answer.
感谢 tp @gbn 提及并要求更清晰准确的答案。
回答by Rishi Bhatnagar
As defined in the link below, you can delete in a straight forward manner
如以下链接中所定义，您可以直接删除
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
http://technet.microsoft.com/en-us/library/ms175486(v=sql.105).aspx
回答by Jason Dam
delete from [mytab]
where [mytab].primarykeyid in
(
select top 1000 primarykeyid
from [mytab]
)
回答by Joe Bourne
SET ROWCOUNT 1000;
DELETE FROM [MyTable] WHERE .....
回答by Hamed elahi
It is fast. Try it:
它很快。尝试一下：
DELETE FROM YourTABLE
FROM (SELECT TOP XX PK FROM YourTABLE) tbl
WHERE YourTABLE.PK = tbl.PK
Replace YourTABLEby table name,
XXby a number, for example 1000,
pkis the name of the primary key field of your table.
替换YourTABLE为表名，
替换XX为数字，例如 1000，
pk是表的主键字段的名称。
