MySQL DELETE FROM以子查询为条件?mysql
我试图做这样的查询：
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 我已经检查了文档，并独自运行了子查询，这一切似乎都已结束。任何人都可以找出这里有什么问题吗？
写回答
-
您无法指定要删除的目标表。
解决方法
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
2020-05-17 09:46:28赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
