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

Question

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,
    active smallint DEFAULT 0,
    PRIMARY KEY (id)
);

-- DEMO DATA
INSERT INTO people(id, name, age, active) 
VALUES
(1, 'Jon', 37, 1),
(2, 'Jack', 23, 0),
(3, 'Peter', 24, 0),
(4, 'Phil', 55, 0);

Create variable:

SELECT @REMOVE := GROUP_CONCAT(id) FROM people WHERE active < 1; -- (2,3,4)

I'm trying to remove concatenated variables from string.

DELETE FROM people WHERE id IN(@REMOVE); -- will delete only first id which is id nr 2

The above SQL removes only first element from the list. In this example, list will contain: (2,3,4). Only the record with id = 2 will be removed. Records with id 3, 4 will remain in the table. See the table before and after in the image below:

I am well aware that I could use on of two solutions like:

Subquery:

-- SOLUTION 1 - USEING NESTED SELECT SUB QUERY WITH AN ALIAS 
DELETE FROM people WHERE id IN(SELECT * FROM (SELECT id FROM people WHERE active < 1) as temp);

Solution 1 is not ideal if we need to run same subquery in different query at a later point, wanting to preserve the original output while running insert, update or delete operations on the same table.

or

Temp table:

CREATE TEMPORARY TABLE temp_remove_people (id int NOT NULL PRIMARY KEY);
INSERT INTO temp_remove_people SELECT id FROM people WHERE active < 1;
DELETE FROM people WHERE id IN(SELECT id FROM temp_remove_people);

This will preseve original select within same session.

I would like to know if it is possible to use concatenated variable in some different way to make it work.

Froggy

I am confused why the first alternative solution cannot work which scales better thangroup_concat? Also, you use do not the outer SELECT * FROM. Can you show problem with data or SQL? – Autoerotism

Basicaly if I would have two tables related by id and if I need to query first table to get set of id, then delete the data from this table and then try to remove data from another table using select from the first table - it would return different results as i would already modify the first table that i select from. So there fore i need to keep a record of selected id so i can refer to it when running query on different table. This example only illustrate the issue with deleting from grup_concat sql variable but my problem is deeper. Tempolary table also solves this issue. – Froggy

This sounds like you have multiple people tables which can be a database design and hence maintenance issue. If you can, try normalizing tables to distinct relations. Usually complex queries are signs of design issues. – Autoerotism

@Autoerotism there are over 150 tables in this database and is pretty well normalized so this is not the case. Thank you for your concern. – Froggy

Actually, many tables does not necessarily indicate normalization. Once again only have one unique relation: one People, one Orders, one Products, etc. Do not use data elements as table entities (e.g., AmazonTable, GoogleTable, MicrosoftTable...) As an example, AdventureWorks (SQL Server's sample database) used in many database courses maintains 70+ tables in normalized distinct entities in clear schemas (i.e, MySQL's databases). No redundancies or repetitive entities. Good luck! – Autoerotism

Answer 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;
DEALLOCATE PREPARE stmt;

Sialagogue

You solved it. Thanks for help and for pointing disadvantage of FIND_IN_SET(). Works like a charm. – Froggy

Answer 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);

DELETE FROM people 
WHERE FIND_IN_SET(id, @REMOVE);

See the demo.

Hendiadys

Answer 3

You could use find_in_set():

where find_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:

where exists (select 1
              from tempids t
              where t.id = p.id
             );

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

Clerkly

Is there any performance difference in running DELETE FROM people WHERE id IN(SELECT id FROM temp_remove_people); vs DELETE FROM people p where exists (select 1 from temp_remove_people t where t.id = p.id ); ? – Froggy

And why do we need "> 0" in where find_in_set(id, @remove) > 0 – Froggy

@Froggy . . . For clarity. Technically, it will work without the > 0, but find_in_set() returns the position where the value is found, so the > 0 makes it clearer that the value is found. – Clerkly