CODEWITHSUNDEEP
mysqlsqlstringcsvwhere-clause
Bohumír Mäsiar
Bohumír Mäsiar

Reputation: 149

Delete multiple rows by id in SQL

I have a big list with id's that I need to delete from a SQL database:

B14260V;B14261;B14261V;B14262;B14264;B14264V;B14265

Currently I delete single items with:

DELETE 
  FROM `ixml_prd_map` 
 WHERE `ixml_prd_map`.`id` = B14260V"

It is somehow possible to batch delete items with the given list?

Upvotes: 0

Views: 1471

Answers (3)

Petr Hejda
Petr Hejda

Reputation: 43521

Use IN operator.

Example:

DELETE FROM `ixml_prd_map` WHERE `ixml_prd_map`.`id` IN ("B14260V", "B14261", "B14261V")

Upvotes: 1

sasal
sasal

Reputation: 201

DELETE FROM ixml_prd_map WHERE id IN (...your id list...)

Upvotes: 1

GMB
GMB

Reputation: 222492

You want find_in_set():

delete from ixml_prd_map
where find_in_set(replace(@mylist, ';', ','), id)

find_in_set() returns the position of an element in a CSV list; it expects the separator to be the comma, so it you really have semi-colons then you need to change that, as shown in the above query.

A better option would be to provide your input as a proper list of values, as in:

delete from ixml_prd_map
where id in ('B14260V', 'B14261', 'B14261V')

Upvotes: 1

Related Questions