Skip to content
Advertisement

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

Is there is a way to delete records using WHERE IN @VARIABLE?

Create variable:

I’m trying to remove concatenated variables from string.

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:

enter image description here

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

Subquery:

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:

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.

Advertisement

Answer

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:

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement