By balu zapps


2013-04-16 05:31:15 8 Comments

I am trying to run a SQL query to delete rows with id's 163 to 265 in a table

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

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

Please tell me the query to do the above action...

5 comments

@Keerthi 2016-05-13 04:34:01

Please try this:

DELETE FROM `table` WHERE id >=163 and id<= 265

@Denny 2017-10-12 06:35:04

Delete Id from table where Id in (select id from table)

@WhatsThePoint 2017-10-12 07:27:59

Could you please format your code by highlighting it and hitting Ctrl+K

@Fabrizio 2017-10-12 08:56:05

I don't think it's the right syntax for a delete statement

@ThienPhuc 2015-10-05 09:00:12

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>

@Barranka 2013-04-16 05:35:59

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;

@balu zapps 2013-04-16 07:22:01

Thank you! I have used BETWEEN and Its executed successfully to my requirement.

@Barranka 2014-12-12 15:02:31

@baluzapps If you find this answer useful, accept it ;)

@Mayur Beldar 2018-11-13 00:24:16

A question related to this: What if I have composite key?

@Barranka 2018-11-13 01:10:59

@MayurBeldar: if you have a new question, post it as a question, not as a comment

@leppie 2013-04-16 05:35:27

You can use BETWEEN:

DELETE FROM table
where id between 163 and 265

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

45 Answered Questions

27 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2426790 View
  • 1650 Score
  • 27 Answer
  • Tags:   sql duplicates

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

37 Answered Questions

15 Answered Questions

[SOLVED] Select n random rows from SQL Server table

  • 2009-05-11 16:19:11
  • John M Gant
  • 327885 View
  • 283 Score
  • 15 Answer
  • Tags:   sql sql-server random

7 Answered Questions

[SOLVED] Best way to delete millions of rows by ID

Sponsored Content