MySQL-当嵌套选择使用该表时如何从表中删除? [英] MySQL - How to delete from table when nested select uses that table?
本文介绍了MySQL-当嵌套选择使用该表时如何从表中删除?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试做这样的事情:
I'm trying to do something like this:
DELETE FROM table_1
WHERE table_1.id IN (SELECT table_1.id
FROM table_1,
table_2,
table_3
WHERE table_1.id = table_2.table_1_id
AND table_2.id = table_3.table_2_id
AND table_3.id = 5)
似乎MySQL不允许我在嵌套的SELECT中使用table_1
.有建议吗?
Seems like MySQL won't let me have table_1
in the nested SELECT. Suggestions?
推荐答案
您可能会收到此错误:
ERROR 1093 (HY000): You can't specify target table 'table_1' for update in FROM clause.
You should be able to do this using MySQL's multiple-table DELETE
syntax instead:
DELETE table_1.*
FROM table_1,
table_2,
table_3
WHERE table_1.id = table_2.table_1_id
AND table_2.id = table_3.table_2_id
AND table_3.id = 5
上面的查询应该可以工作,但是作为一般规则,我建议改用ANSI JOIN语法:
The above query should work, but as a general rule I would recommend using ANSI JOIN syntax instead:
DELETE table_1.*
FROM table_1
INNER JOIN table_2 on table_2.table_1_id = table_1.id
INNER JOIN table_3 on table_3.table_2_id = table_2.id
WHERE table_3.id = 5
这篇关于MySQL-当嵌套选择使用该表时如何从表中删除?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文