How to Delete from a Table Where Id Is in a List of Ids

How to delete from a table where ID is in a list of IDs?

Your question almost spells the SQL for this:

DELETE FROM table WHERE id IN (1, 4, 6, 7)

Delete many rows from a table using id in Mysql

The best way is to use IN statement :

DELETE from tablename WHERE id IN (1,2,3,...,254);

You can also use BETWEEN if you have consecutive IDs :

DELETE from tablename WHERE id BETWEEN 1 AND 254;

You can of course limit for some IDs using other WHERE clause :

DELETE from tablename WHERE id BETWEEN 1 AND 254 AND id<>10;

Select list of IDs from one table and delete all IDs from a second table in SQL Server

This might get you there:

delete from Table2 where ID in (select ID from Table1 where UserID = '5')

You're right to think there is a better way in SQL

Delete rows from mysql server from a list of ID's C#

You would probably use an IN clause in your DELETE:

DELETE FROM `EmployeeTable` WHERE EmployeeID IN (2, 3, 4, 5, ...)

This could be implemented with the String.Join method to generate the list:

var query = "DELETE FROM `EmployeeTable` WHERE EmployeeID IN (" +
    String.Join(",", myArray) + ")";

How to delete rows with IDs in a specific range?

DELETE FROM table_name where id >= 1043 and id < 1101; 

How to remove item from list which Id is not in list of Id's

You can try and do this:
HashSet<int> listOfProdictids = new HashSet<int>(db.OrderedProducts.Where(x => x.Id == 1).Select(x => x.ProductId).toArray());

And then:

List<ProductVM> products = db.Products.Where(x => !listOfProducts.Contains(x.id)).Select(x => x).toList();

Deleting records in MySQL WHERE id IN (@VARIABLE) -- (2,3,4)

The suggestion of FIND_IN_SET() spoils any opportunity to optimize that query with an index.

You would like to treat the variable as a list of discrete integers, not as a string that happens to contain commas and digits. This way it can use an index to optimize the matching.

To do this, you have to use a prepared statement:

SET @sql = CONCAT('DELETE FROM people WHERE id IN(', @REMOVE, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

why my `delete where id in` statement drop all records in the table?

...outer sql select id from () tmp selects a non exsit field id
which lead to an error, but mysql execute this sql and delete all
records.

I want to konw why this happened.

This subquery will not run on its own:

select id from (
    select min(id) from reply group by ex_type and ex_id having count(1) > 1
) tmp
/* SQL Error (1054): Unknown column 'id' in 'field list' */

But when it is run inside a subquery then according to scope resolution rules the id column resolves to the id column of the outer query since the requested column is not present in the FROM clause. The query is essentially like:

delete from reply where id in (
    select reply.id from (
        select min(id) from reply group by ex_type and ex_id having count(1) > 1
    ) tmp
)
/* Affected rows: 4  Found rows: 0  Warnings: 0  Duration for 1 query: 0.031 sec. */

The condition is true for all rows since 1 IN (1), 2 IN (2), 3 IN (3)... are all true. Fixing the typo (group by ex_type and ex_id) will not fix the problem, change your query to this:

delete from reply where id in (
    select tmp.id from (
        select min(id) as id from reply group by ex_type, ex_id having count(1) > 1
    ) tmp
)

How to remove data from mysql not in range of IDs

You can remove records according to the list of ids like this:

delete from table where id in (3,4,6,8,9)

or (to delete everything except ids in list)

 delete from table where id not in (3,4,6,8,9)

---

Related Topics