MySQL MYSQL删除一个查询条件独特的每一行多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14904067/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL delete multiple rows in one query conditions unique to each row
提问by srchulo
So I know in MySQLit'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'
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?
有谁知道这样做的方法吗？或者不可能？
回答by fthiella
回答by Richard A Quadling
A slight extension to the answer given, so, hopefully useful to the asker and anyone else looking.
轻微的扩展，给出的答案，所以，希望对大家有用提问者和其他人看。
You can also SELECTthe values you want to delete. But watch out for the Error 1093 - You can't specify the target table for update in FROM clause.
您还SELECT可以删除要删除的值。但要注意错误 1093 - 您不能在 FROM 子句中指定要更新的目标表。
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.
理查德.
