81

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?

Improve this question

3 Answers 3

Reset to default
141

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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.
If anyone is curious, this is the bug that was fixed in 5.7.3 that @ep4169 references: bugs.mysql.com/bug.php?id=31188
14

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.

Improve this answer

5 Comments

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 ... <sigh>! :-)
What do you mean by "Whitesmith"?
Whitesmith is a code formatting style en.wikipedia.org/wiki/Indent_style#Whitesmiths_style
wow that's an odd one. I don't quite remember anyone using it at all... How old are you?
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.
-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)
Improve this answer

1 Comment

The question asks how to do it in MySQL without Python.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.