SQl 从表中删除前 100 个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/809294/
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
SQl Delete top 100 from table
提问by Bryan
I am trying to delete the all but the most recent 3,000 items in a table. The table has 105,000 records.
我正在尝试删除表中除最近 3,000 项之外的所有项。该表有 105,000 条记录。
I am trying this, but an error is generated incorrect syntax.
我正在尝试此操作，但生成的错误语法不正确。
delete tRealtyTrac where creation in( select top 103000 from tRealtyTrac order by creation)
回答by Brian
The delete syntax is going to be slightly different from what you have. An example would be:
删除语法将与您拥有的略有不同。一个例子是：
DELETE FROM tRealtyTrac
WHERE creation in( select top 103000 creation from tRealtyTrac order by creation)
Notice how there is the "from" keyword. This is saying we want to delete from the table called tRealtyTrac
注意“from”关键字是如何存在的。这是说我们要从名为 tRealtyTrac 的表中删除
The one problem I foresee with this is, you are probably going to want to not use creation...
我预见到的一个问题是，您可能不想使用创建...
Instead:
反而：
DELETE FROM tRealtyTrac
WHERE someuniqueidcolumnlikeakeyofsomesort in( select top 103000 someuniqueidcolumnlikeakeyofsomesort from tRealtyTrac order by creation)
Otherwise you may delete more than you intended.
否则，您删除的内容可能会超出您的预期。
回答by Andy White
The inner query needs to be:
内部查询需要是：
select top 103000 creationfrom ...
从...中选择前 103000 个创作
回答by AdvanTiSS
As for me, CTE is the better solution for ordered deletion
对我来说，CTE 是更好的有序删除解决方案
;WITH records_delete AS (
select top 103000 creation
from tRealtyTrac
order by creation)
DELETE records_delete
回答by NotMe
A super easy way to do this:
一个超级简单的方法来做到这一点：
select top 3001 from tRealtyTrac order by creation desc
take the last one's date then delete tRealtyTrac where creation < 'thedateyoufound'
按创建说明从 tRealtyTrac 订单中选择前 3001 个
取最后一个日期然后删除 tRealtyTrac where creation < 'thedateyoufound'
But Andy has a good idea too. ;)
但安迪也有一个好主意。;)
回答by backslash17
Try this:
尝试这个：
DELETE FROM tRealtyTrac WHERE creation IN (SELECT top 103000 * FROM tRealtyTrac ORDER by creation)
You forgot the fields in tRealtyTrac (I used an asterisk to select all but you can make a list of them or only one). You also forgot the FROM clause.
您忘记了 tRealtyTrac 中的字段（我使用星号选择所有字段，但您可以列出它们或仅列出一个）。您还忘记了 FROM 子句。
