MySQL delete multiple rows in one query conditions unique to each row

mysql unique-constraint multiple-records
srchulo picture srchulo · Feb 15, 2013 · Viewed 127k times · Source

So I know in MySQL it's possible to insert multiple rows in one query like so:

INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6)

I would like to delete multiple rows in a similar way. I know it's possible to delete multiple rows based on the exact same conditions for each row, i.e.

DELETE FROM table WHERE col1='4' and col2='5'

or

DELETE FROM table WHERE col1 IN (1,2,3,4,5)

However, what if I wanted to delete multiple rows in one query, with each row having a set of conditions unique to itself? Something like this would be what I am looking for:

DELETE FROM table WHERE (col1,col2) IN (1,2),(3,4),(5,6)

Does anyone know of a way to do this? Or is it not possible?

Answer

fthiella picture fthiella · Feb 15, 2013

You were very close, you can use this:

DELETE FROM table WHERE (col1,col2) IN ((1,2),(3,4),(5,6))

Please see this fiddle.

Related questions

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. …

Read More
MySQL - Make an existing Field Unique

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this …

Read More
Does MySQL ignore null values on unique constraints?

I have an email column that I want to be unique. But I also want it to accept null values. Can my database have 2 null emails that way?

Read More