MySQL DELETE FROM with subquery as条件
MySQL DELETE FROM with subquery as条件
提问于 2010-12-17 22:15:48
我正在尝试执行这样的查询：
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不允许在条件的子查询中使用要删除的表。
回答 9
Stack Overflow用户
回答已采纳
发布于 2010-12-17 22:23:33
不能指定要删除的目标表。
一种解决方法
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);Stack Overflow用户
发布于 2012-10-19 15:42:39
对于在使用子查询时发现这个问题想要删除的其他人，我把这个例子留给你，让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将很高兴地执行您所要求的操作。
Stack Overflow用户
发布于 2010-12-17 22:24:36
别名应包含在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
);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4471277
复制相关文章
相似问题