In MySQL how do I use a user defined variable in a WHERE IN clause?

Question

I don't understand why this does not work. Can someone explain what I need to do?

SET @my_list = '2,6,8,10,12,13,14,18,19,21';

DELETE FROM my_table WHERE my_table.table_id IN (@my_list);

possible duplicate of [Pass string into SQL WHERE IN](http://stackoverflow.com/questions/6945223/pass-string-into-sql-where-in) — martin clayton, Oct 18 '11 at 23:40

score 1 · Accepted Answer · answered Oct 18 '11 at 23:43

1

It's interpreting @my_list as a single id and so you're asking it to delete from my_table where your id is the string "2,6,8,10,12,13,14,18,19,21"

You could try

SET @my_list = '2,6,8,10,12,13,14,18,19,21';
SET @exec = CONCAT('DELETE FROM my_table WHERE my_table.table_id IN (', @my_list ,')');
EXECUTE (@exec);

answered Oct 18 '11 at 23:43

bombnomnom

Thanks for answering. Is there a way to make my_list into a an actual list type that mysql understands, so I wouldn't have to do the concat thing? I just want to plug a list in to a set of delete queries that all have to use the same list. It's because theres a bunch of tables that have foreign key relationships and so they have to all have the same list of ids removed. – Jesse Greathouse Oct 19 '11 at 12:48

1 Answers1