sql - 使用子查询作为条件的MySQL DELETE FROM
我正在尝试这样的查询：
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
);
你可以告诉我，如果同一个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
我检查了文档，并自行运行子查询，这一切似乎都检查出来。 任何人都可以弄清楚这里有什么问题吗？
更新：如下所述，MySQL不允许您正在删除的表在子查询中用于该条件。
7个解决方案
217 votes
对于那些在使用子查询时发现这个问题想要删除的人，我给你留下这个例子来说明MySQL(即使有些人似乎认为无法完成)：
DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
FROM tableE
WHERE arg = 1 AND foo = 'bar');
会给你一个错误：
ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause
但是这个查询：
DELETE e.*
FROM tableE e
WHERE id IN (SELECT id
FROM (SELECT id
FROM tableE
WHERE arg = 1 AND foo = 'bar') x);
会工作得很好：
Query OK, 1 row affected (3.91 sec)
将子查询包装在另一个子查询中(此处命名为x)，MySQL将很乐意按您的要求执行操作。
CodeReaper answered -06-16T07:27:28Z
31 votes
您无法指定要删除的目标表。
解决方法
create table term_hierarchy_backup (tid int(10));
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);
ajreal answered -06-16T07:26:36Z
31 votes
别名应包含在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
);
James Wiseman answered -06-16T07:27:53Z
6 votes
您需要在delete语句中再次引用别名，例如：
DELETE th FROM term_hierarchy AS th
....
正如MySQL文档中所述。
JNK answered -06-16T07:28:26Z
5 votes
我以一种略微不同的方式接近它，它对我有用;
我需要从引用conditions表的表中删除sl1，其中不再有任何条件行。 一个看家脚本基本上。 这给了我错误 - 您无法指定要删除的目标表。
所以在这里寻找灵感我想出了下面的查询，它工作得很好。这是因为它创建了一个临时表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`
)
适合我。
Darren Edwards answered -06-16T07:29:08Z
3 votes
是不是删除中的“in”子句...哪里，效率极低，如果从子查询中返回大量值？ 不确定为什么你不会只是内部(或右)从要删除的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)
也许它在“MySQL不允许它”中得到解答，但是，它对我来说工作正常提供我确保完全澄清要删除的内容(DELETE T FROM Target AS T)。 使用Join in MySQL删除可澄清DELETE / JOIN问题。
Jeff answered -06-16T07:29:42Z
1 votes
如果您想使用2个查询执行此操作，则可以始终执行与此类似的操作：
1)从表中抓取ID：
SELECT group_concat(id) as csv_result FROM your_table WHERE whatever = 'test' ...
然后用鼠标/键盘或编程语言将结果复制到下面的XXX：
2) DELETE FROM your_table WHERE id IN ( XXX )
也许你可以在一个查询中执行此操作，但这是我更喜欢的。
TomoMiha answered -06-16T07:30:28Z
