MySQL DELETE FROM以子查询为条件

  • Z时代
  • 2024-01-10
  • 分类：问答

我试图做这样的查询：

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

回答：

您无法指定要删除的目标表。

解决方法

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 FROM以子查询为条件 的全部内容， 来源链接： utcz.com/qa/424823.html

回到顶部