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

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?

This question is related to mysql unique-constraint multiple-records

The answer is


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.


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)

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to unique-constraint

MySQL delete multiple rows in one query conditions unique to each row Unique constraint violation during insert: why? (Oracle) How to drop a unique constraint from table column? How can I create a unique constraint on my column (SQL Server 2008 R2)? MySQL - Make an existing Field Unique Does MySQL ignore null values on unique constraints? @UniqueConstraint annotation in Java SQLite table constraint - unique on multiple columns Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created? How do I specify unique constraint for multiple columns in MySQL?

Examples related to multiple-records

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