如何在 id = (x to y) 的 SQL 中删除多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16029441/
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 multiple rows in SQL where id = (x to y)
提问by balu zapps
I am trying to run a SQL query to delete rows with id's 163 to 265 in a table
我正在尝试运行 SQL 查询以删除表中 ID 为 163 到 265 的行
I tried this to delete less number of rows
我试着用这个来删除更少的行数
DELETE FROM `table` WHERE id IN (264, 265)
But when it comes to delete 100's of rows at a time, Is there any query similar to above method I am also trying to use this kind of query but failed to execute it
但是当涉及一次删除 100 行时，是否有任何类似于上述方法的查询我也尝试使用这种查询但未能执行它
DELETE FROM `table` WHERE id IN (SELECT * FROM table WHERE id = )
Please tell me the query to do the above action...
请告诉我执行上述操作的查询...
回答by Barranka
If you need to delete based on a list, you can use IN:
如果需要根据列表删除，可以使用IN：
DELETE FROM your_table
WHERE id IN (value1, value2, ...);
If you need to delete based on the result of a query, you can also use IN:
如果需要根据查询结果进行删除，也可以使用IN：
DELETE FROM your_table
WHERE id IN (select aColumn from ...);
(Notice that the subquery must return only one column)
（注意子查询必须只返回一列）
If you need to delete based on a range of values, either you use BETWEENor you use inequalities:
如果您需要根据一系列值进行删除，请使用BETWEEN或使用不等式：
DELETE FROM your_table
WHERE id BETWEEN bottom_value AND top_value;
or
或者
DELETE FROM your_table
WHERE id >= a_value AND id <= another_value;
回答by leppie
You can use BETWEEN:
您可以使用BETWEEN：
DELETE FROM table
where id between 163 and 265
回答by Keerthi
Please try this:
请试试这个：
DELETE FROM `table` WHERE id >=163 and id<= 265
回答by Denny
Delete Id from table where Id in (select id from table)
回答by ThienPhuc
CREATE PROC [dbo].[sp_DELETE_MULTI_ROW]
@CODE XML
,@ERRFLAG CHAR(1) = '0' OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DELETE tb_SampleTest
WHERE
CODE IN(
SELECT Item.value('.', 'VARCHAR(20)')
FROM @CODE.nodes('RecordList/ID') AS x(Item)
)
IF @@ROWCOUNT = 0
SET @ERRFLAG = 200
SET NOCOUNT OFF
Get string value delete
获取字符串值删除
<RecordList>
<ID>1</ID>
<ID>2</ID>
</RecordList>
