79

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
srchulo
  • 5,143
  • 4
  • 43
  • 72

3 Answers3

135

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.

fthiella
  • 48,073
  • 15
  • 90
  • 106
  • 11
    Please note that in MySQL 5.6 this will perform poorly as tuple compares end up doing a table scan. You should prefer "WHERE (col1=1 AND col2=2) OR (col1=3 AND col2=4)..." for better performance. This is fixed in 5.7. – ep4169 Mar 11 '16 at 17:26
  • 1
    If anyone is curious, this is the bug that was fixed in 5.7.3 that @ep4169 references: https://bugs.mysql.com/bug.php?id=31188 – Kip Feb 09 '17 at 20:26
12

A slight extension to the answer given, so, hopefully useful to the asker and anyone else looking.

You can also SELECT the values you want to delete. But watch out for the Error 1093 - You can't specify the target table for update in FROM clause.

DELETE FROM
    orders_products_history
WHERE
    (branchID, action) IN (
    SELECT
        branchID,
        action
    FROM
        (
        SELECT
            branchID,
            action
        FROM
            orders_products_history
        GROUP BY
            branchID,
            action
        HAVING
            COUNT(*) > 10000
        ) a
    );

I wanted to delete all history records where the number of history records for a single action/branch exceed 10,000. And thanks to this question and chosen answer, I can.

Hope this is of use.

Richard.

Richard A Quadling
  • 3,769
  • 30
  • 40
  • 1
    Formatting is to provide hierarchy and dependency. Sure 1 line of SQL would be the same but unreadable. Pretty much anything is better than a 1 liner. Of course, I use Whitesmith for my code and no one likes that either. "Too much white space" I hear them whine. Pah! I say. Use an IDE that formats it for YOUR preference for editing and to the repo's preference when you save. Oh... you use vi ... ! :-) – Richard A Quadling Sep 21 '15 at 11:31
  • What do you mean by "Whitesmith"? – Pacerier Sep 22 '15 at 01:39
  • Whitesmith is a code formatting style https://en.wikipedia.org/wiki/Indent_style#Whitesmiths_style – Richard A Quadling Sep 22 '15 at 12:46
  • wow that's an odd one. I don't quite remember anyone using it at all... How old are you? – Pacerier Sep 23 '15 at 03:14
  • 2
    Nearly 50. The way to think of it is if you used BASIC, Pascal, COBOL, then the BEGIN/END would always be on separate lines and indented. I just followed that when I want to {}. And as {} are around the compound statement, they are subordinate to the if()/while()/etc. so are indented. It is a logical choice, but just not very popular. – Richard A Quadling Sep 23 '15 at 22:43
-1

Took a lot of googling but here is what I do in Python for MySql when I want to delete multiple items from a single table using a list of values.

#create some empty list
values = []
#continue to append the values you want to delete to it
#BUT you must ensure instead of a string it's a single value tuple
values.append(([Your Variable],))
#Then once your array is loaded perform an execute many
cursor.executemany("DELETE FROM YourTable WHERE ID = %s", values)
Tom Krier
  • 1