如何删除 SQL 中 id = (x 到 y)的多行

我试图运行一个 SQL 查询来删除表中 id 为163到265的行

我尝试这样删除较少的行数

    DELETE FROM `table` WHERE id IN (264, 265)

但是当一次删除100行时，有没有类似于上述方法的查询 我也尝试使用这种类型的查询，但未能执行它

    DELETE FROM `table` WHERE id IN (SELECT * FROM table WHERE id = )

请告诉我做上述动作的查询..。

384364 次浏览
小开

You can use BETWEEN:

DELETE FROM table
where id between 163 and 265
小开

If you need to delete based on a list, you can use 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:

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 BETWEEN or you use inequalities:

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;
小开 
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>
小开

Please try this:

DELETE FROM `table` WHERE id >=163 and id<= 265
小开 
SELECT * FROM your_table ORDER BY DESC

Get the range that you want to delete Ex: 3 to 10

 DELETE FROM your_table
WHERE id BETWEEN 3 AND 10;

Make sure to add min value fist in BETWEEN Clause


提交答案