MySQL DELETE FROM以子查询为条件 [英] MySQL DELETE FROM with subquery as condition

查看:526
本文介绍了MySQL DELETE FROM以子查询为条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试执行以下查询:

I am trying to do a query like this:

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的父母关系.但是,这会产生语法错误:

As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:

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

我已经检查了文档,并独自运行了子查询,所有这些似乎都已签出.任何人都可以找出这里有什么问题吗?

I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what's wrong here?

更新:如下面的回答,MySQL不允许在子查询中使用要删除的表作为条件.

Update: As answered below, MySQL does not allow the table you're deleting from be used in a subquery for the condition.

推荐答案

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

You cannot specify target table for 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 FROM以子查询为条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆