MySQL DELETE FROM以子查询为条件

Question

我试图做这样的查询：

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

);

您可能会说，如果相同的提示还有其他父母，我想删除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不允许在子查询中使用该条件删除要删除的表。

Answer 1

您无法指定要删除的目标表。解决方法create table term_hierarchy_backup (tid int(10)); <- check data typeinsert into term_hierarchy_backup&nbsp;SELECT DISTINCT(th1.tid)FROM term_hierarchy AS th1INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)WHERE th1.parent = 1015;DELETE FROM term_hierarchy AS thWHERE th.parent = 1015 AND th.tid IN (select tid from term_hierarchy_backup);

Answer 2

对于其他发现此问题并希望在使用子查询时删除的问题，我将这个示例留给您，以取代MySQL（即使有些人似乎认为无法做到）：DELETE e.*FROM tableE eWHERE id IN (SELECT id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM tableE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;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 eWHERE id IN (SELECT id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM (SELECT id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM tableE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE arg = 1 AND foo = 'bar') x);会很好地工作：Query OK, 1 row affected (3.91 sec)将子查询包装在另一个子查询（这里称为x）中，MySQL会很乐意完成您的要求。

Answer 3

别名应包含在DELETE关键字之后：DELETE thFROM term_hierarchy AS thWHERE th.parent = 1015 AND th.tid IN&nbsp;(&nbsp; &nbsp; SELECT DISTINCT(th1.tid)&nbsp; &nbsp; FROM term_hierarchy AS th1&nbsp; &nbsp; INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)&nbsp; &nbsp; WHERE th1.parent = 1015);