MySQL DELETE FROM 以子查询为条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4471277/
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 FROM with subquery as condition
提问by mikl
I am trying to do a query like this:
我正在尝试进行这样的查询：
DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015
);
As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:
您可能会说，如果同一个 tid 有其他父母，我想删除与 1015 的父关系。但是，这给我带来了语法错误：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS th
WHERE th.parent = 1015 AND th.tid IN (
SELECT DISTINCT(th1.tid)
FROM ter' at line 1
I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what's wrong here?
我已经检查了文档，并自行运行了子查询，这一切似乎都已完成。谁能弄清楚这里出了什么问题？
Update: As answered below, MySQL does not allow the table you're deleting from be used in a subquery for the condition.
更新：如下所述，MySQL 不允许您删除的表用于条件的子查询。
采纳答案by ajreal
You cannot specify target table for delete.
您不能指定要删除的目标表。
A workaround
解决方法
create table term_hierarchy_backup (tid int(10)); <- check data type
insert into term_hierarchy_backup
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015;
DELETE FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN (select tid from term_hierarchy_backup);
回答by CodeReaper
For others that find this question looking to delete while using a subquery, I leave you this example for outsmarting MySQL (even if some people seem to think it cannot be done):
对于在使用子查询时发现这个问题想要删除的其他人，我给你留下了这个例子来超越 MySQL（即使有些人似乎认为它无法完成）：
DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
FROM tableE
WHERE arg = 1 AND foo = 'bar');
will give you an error:
会给你一个错误：
ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause
However this query:
但是这个查询：
DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
FROM (SELECT id
FROM tableE
WHERE arg = 1 AND foo = 'bar') x);
will work just fine:
会工作得很好：
Query OK, 1 row affected (3.91 sec)
Wrap your subquery up in an additional subquery (here named x) and MySQL will happily do what you ask.
将您的子查询包装在一个额外的子查询中（这里命名为 x），MySQL 会很乐意地按照您的要求执行。
回答by James Wiseman
The alias should be included after the DELETEkeyword:
别名应包含在DELETE关键字之后：
DELETE th
FROM term_hierarchy AS th
WHERE th.parent = 1015 AND th.tid IN
(
SELECT DISTINCT(th1.tid)
FROM term_hierarchy AS th1
INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th1.parent = 1015
);
回答by JNK
You need to refer to the alias again in the delete statement, like:
您需要在删除语句中再次引用别名，例如：
DELETE th FROM term_hierarchy AS th
....
回答by Darren Edwards
I approached this in a slightly different way and it worked for me;
我以稍微不同的方式处理这个问题，它对我有用；
I needed to remove secure_linksfrom my table that referenced the conditionstable where there were no longer any condition rows left. A housekeeping script basically. This gave me the error - You cannot specify target table for delete.
我需要secure_links从我的表中删除引用了conditions不再有任何条件行的表。基本上是一个看家脚本。这给了我错误 - 您无法指定要删除的目标表。
So looking here for inspiration I came up with the below query and it works just fine.
This is because it creates a temporary table sl1that is used as the reference for the DELETE.
所以在这里寻找灵感我想出了下面的查询，它工作得很好。这是因为它创建了一个临时表sl1，用作 DELETE 的引用。
DELETE FROM `secure_links` WHERE `secure_links`.`link_id` IN
(
SELECT
`sl1`.`link_id`
FROM
(
SELECT
`sl2`.`link_id`
FROM
`secure_links` AS `sl2`
LEFT JOIN `conditions` ON `conditions`.`job` = `sl2`.`job`
WHERE
`sl2`.`action` = 'something' AND
`conditions`.`ref` IS NULL
) AS `sl1`
)
Works for me.
为我工作。
回答by Jeff
Isn't the "in" clause in the delete ... where, extremely inefficient, if there are going to be a large number of values returned from the subquery? Not sure why you would not just inner (or right) join back against the original table from the subquery on the ID to delete, rather than us the "in (subquery)".?
如果子查询返回大量值，delete 中的“in”子句不是非常低效吗？不知道为什么你不只是inner（或right）从子查询上的原始表中加入要删除的ID，而不是我们的“in（子查询）”。？
DELETE T FROM Target AS T
RIGHT JOIN (full subquery already listed for the in() clause in answers above) ` AS TT ON (TT.ID = T.ID)
And maybe it is answered in the "MySQL doesn't allow it", however, it is working fine for me PROVIDED I make sure to fully clarify what to delete (DELETE T FROM Target AS T). Delete with Join in MySQLclarifies the DELETE / JOIN issue.
也许它在“MySQL 不允许它”中得到了回答，但是，它对我来说工作正常，前提是我确保完全阐明要删除的内容（从目标 AS T 删除 T）。 在 MySQL 中使用 Join 删除澄清了 DELETE / JOIN 问题。
回答by TomoMiha
If you want to do this with 2 queries, you can always do something similar to this:
如果你想用 2 个查询来做到这一点，你总是可以做类似的事情：
1) grab ids from the table with:
1）从表中获取ID：
SELECT group_concat(id) as csv_result FROM your_table WHERE whatever = 'test' ...
Then copy result with mouse/keyboard or programming language to XXX below:
然后用鼠标/键盘或编程语言将结果复制到下面的XXX：
2) DELETE FROM your_table WHERE id IN ( XXX )
Maybe you could do this in one query, but this is what I prefer.
也许你可以在一个查询中做到这一点，但这是我更喜欢的。
回答by rc.adhikari
@CodeReaper, @BennyHill: It works as expected.
@CodeReaper、@BennyHill：它按预期工作。
However, I wonder the time complexity for having millions of rows in the table? Apparently, it took about 5msto execute for having 5k records on a correctly indexed table.
但是，我想知道表中有数百万行的时间复杂度？显然，5ms在正确索引的表上有 5k 条记录需要执行。
My Query:
我的查询：
SET status = '1'
WHERE id IN (
SELECT id
FROM (
SELECT c2.id FROM clusters as c2
WHERE c2.assign_to_user_id IS NOT NULL
AND c2.id NOT IN (
SELECT c1.id FROM clusters AS c1
LEFT JOIN cluster_flags as cf on c1.last_flag_id = cf.id
LEFT JOIN flag_types as ft on ft.id = cf.flag_type_id
WHERE ft.slug = 'closed'
)
) x)```
Or is there something we can improve on my query above?
回答by YakovGdl35
you can use the alias in this way on the delete statement
您可以在删除语句中以这种方式使用别名
DELETE th.*
FROM term_hierarchy th
INNER JOIN term_hierarchy th2 ON (th1.tid = th2.tid AND th2.parent != 1015)
WHERE th.parent = 1015;
