title: Deleting records in MySQL WHERE id IN (@VARIABLE) -- (2,3,4)
body: <p>Is there is a way to delete records using <code>WHERE IN @VARIABLE</code>?</p>
  <pre><code>-- 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);
  </code></pre>
  <p>Create variable:</p>
  <pre><code>SELECT @REMOVE := GROUP_CONCAT(id) FROM people WHERE active &lt; 1; -- (2,3,4)
  </code></pre>
  <p>I'm trying to remove concatenated variables from string.</p>
  <pre><code>DELETE FROM people WHERE id IN(@REMOVE); -- will delete only first id which is id nr 2
  </code></pre>
  <p>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:</p>
  <p><a href="https://i.stack.imgur.com/ugeM3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ugeM3.png" alt="enter image description here" /></a></p>
  <p>I am well aware that I could use on of two solutions like:</p>
  <p>Subquery:</p>
  <pre><code>-- SOLUTION 1 - USEING NESTED SELECT SUB QUERY WITH AN ALIAS 
  DELETE FROM people WHERE id IN(SELECT * FROM (SELECT id FROM people WHERE active &lt; 1) as temp);
  </code></pre>
  <p>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.</p>
  <p>or</p>
  <p>Temp table:</p>
  <pre><code>CREATE TEMPORARY TABLE temp_remove_people (id int NOT NULL PRIMARY KEY);
  INSERT INTO temp_remove_people SELECT id FROM people WHERE active &lt; 1;
  DELETE FROM people WHERE id IN(SELECT id FROM temp_remove_people);
  </code></pre>
  <p>This will preseve original select within same session.</p>
  <p>I would like to know if it is possible to use concatenated variable in some different way to make it work.</p>