MySQL在一个查询条件中删除每一行的多个行

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

我知道在MySQL中，可以在一个查询中插入多个行

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'

或

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?

有人知道怎么做吗?还是不可能?

2 个解决方案

#1

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.

请看到这个小提琴。

#2

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.

您还可以选择要删除的值。但是请注意错误1093 -您不能指定从子句中更新的目标表。

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.

我想删除所有历史记录，其中单个操作/分支的历史记录数超过10,000。感谢这个问题和选择的答案，我可以。

Hope this is of use.

希望这是有用的。

Richard.

理查德。

#1