SQL 删除特定范围内的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8225036/
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 Records within a specific Range
提问by Royi Namir
This is probably a very simple question for somebody with experience, but I just wanted to know the safest way to delete a couple of hundred records in an SQL table that fall between a specific range.
对于有经验的人来说，这可能是一个非常简单的问题，但我只是想知道删除 SQL 表中位于特定范围之间的几百条记录的最安全方法。
For example I need to delete rows with an ID between 79 & 296:
例如，我需要删除 ID 介于 79 和 296 之间的行：
My worry is if I say delete everything with an ID (>79 AND < 296) then it may literally wipe the whole table.
我担心的是，如果我说删除带有 ID (>79 AND < 296) 的所有内容，那么它实际上可能会擦除整个表格。
回答by Royi Namir
if you use Sql Server
如果您使用 Sql Server
delete from Table where id between 79 and 296
Afteryour edit :you now clarified that you want :
后您的编辑：您现在澄清，你想：
ID (>79 AND < 296)
ID (>79 AND < 296)
So use this :
所以使用这个：
delete from Table where id > 79 and id < 296
delete from Table where id > 79 and id < 296
回答by Micha? Powaga
You gave a condition ID (>79 and < 296) then the answer is:
您给出了条件 ID（> 79 和 < 296），那么答案是：
delete from tab
where id > 79 and id < 296
this is the same as:
这与：
delete from tab
where id between 80 and 295
if idis an integer.
如果id是整数。
All answered:
都回答：
delete from tab
where id between 79 and 296
this is the same as:
这与：
delete from tab
where id => 79 and id <= 296
Mind the difference.
注意区别。
回答by TheTiger
you can also just change your delete to a select *
您也可以将删除更改为选择 *
and test your selection
并测试您的选择
the records selected will be the same as the ones deleted
选择的记录将与删除的记录相同
you can also wrap your statement in a begin / rollback if you are not sure - test the statement then if all is good remove rollback
如果您不确定，您也可以将您的语句包装在开始/回滚中 - 测试该语句，如果一切正常，请删除回滚
for example
例如
SELECT * FROM table WHERE id BETWEEN 79 AND 296
SELECT * FROM table WHERE id BETWEEN 79 AND 296
will show all the records matching the where if they are the wants you 'really' want to delete then use
将显示与 where 匹配的所有记录，如果它们是您“真正”想要删除的需要，然后使用
DELETE FROM table WHERE id BETWEEN 79 AND 296
DELETE FROM table WHERE id BETWEEN 79 AND 296
You can also create a trigger / which catches deletes and puts them into a history table
您还可以创建一个触发器/它捕获删除并将它们放入历史表中
so if you delete something by mistake you can always get it back
所以如果你误删了一些东西，你总能把它找回来
(keep your history table records no older than say 6 months or whatever business rules say)
（保持您的历史表记录不超过 6 个月或任何业务规则规定）
回答by yosh
DELETE FROM table_name
WHERE id BETWEEN 79 AND 296;
回答by CSharpened
If you write it as the following in SQL server then there would be no danger of wiping the database table unless all of the values in that table happen to actually be between those values:
如果您在 SQL Server 中将其编写为以下内容，则不会有擦除数据库表的危险，除非该表中的所有值实际上都在这些值之间：
DELETE FROM [dbo].[TableName] WHERE [TableName].[IdField] BETWEEN 79 AND 296
回答by lloydom
My worry is if I say delete evertything with an ID (>79 AND < 296) then it may literally wipe the whole table...
我担心的是，如果我说删除带有 ID (>79 AND < 296) 的所有内容，那么它实际上可能会擦除整个表格...
That wont happen because you will have a where clause. What happens is that, if you have a statement like delete * from Table1 where id between 70 and 1296, the first thing that sql query processor will do is to scan the table and look for those records in that range and then apply a delete.
这不会发生，因为你会有一个 where 子句。发生的情况是，如果您有类似 的语句delete * from Table1 where id between 70 and 1296，sql 查询处理器将做的第一件事就是扫描表并查找该范围内的那些记录，然后应用删除。
回答by Ali
You can use this way because id can not be sequential in all cases.
您可以使用这种方式，因为 id 不能在所有情况下都是顺序的。
SELECT *
FROM `ht_news`
LIMIT 0 , 30
