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

所以我知道在 MySQL中可以像这样在一个查询中插入多行:

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

我想以类似的方式删除多行。我知道根据每一行的完全相同的条件删除多行是可能的，即。

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

或者

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

但是，如果我想在一个查询中删除多个行，并且每个行都有一组独特的条件，该怎么办呢？这就是我要找的东西:

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

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

148605 次浏览
小开
最佳答案

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.

小开

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)

提交答案