以子查询为条件的MySQL删除

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (26)

我试图执行这样的查询:

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不允许在条件的子查询中使用要删除的表。

提问于
用户回答回答于

不能为DELETE指定目标表。

解决办法

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);
用户回答回答于

对于那些在使用子查询时发现这个问题的人来说,我留下这个例子是为了比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将很高兴地按照您的要求执行。

扫码关注云+社区