Deleting Records In Mysql Where Id In (@variable) -- (2,3,4)

Is there is a way to delete records using WHERE IN @VARIABLE? -- DEMO TABLE CREATE TABLE people ( id int AUTO_INCREMENT NOT NULL, name varchar(100), age int, activ

Solution 1:

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;
DEALLOCATEPREPARE stmt;

Solution 2:

The comma separated list that is returned by GROUP_CONCAT() is a string and you can use a function like FIND_IN_SET() to check the existence of a value in that string:

SET@REMOVE= (SELECT GROUP_CONCAT(id) FROM people WHERE active <1);

DELETEFROM people 
WHERE FIND_IN_SET(id, @REMOVE);

See the demo.

Solution 3:

You could use find_in_set():

wherefind_in_set(id, @remove) > 0

However, I question your entire approach. You are storing ids in strings, and the ids are originally numbers. That is a bad thing.

Instead, just store the values as a temporary table instead of a string. Then you can use the table with in or exists:

whereexists (select1from tempids t
              where t.id = p.id
             );

This also allows you add an index to the table to improve performance.