MySQL DELETE FROM与IN条件的UNION子查询 [英] MySQL DELETE FROM with UNION subquery by IN condition
问题描述
我遇到了一个奇怪的SQL错误.最后一个查询不起作用.当然,我可以将DELETE分为三个查询,但是我真的很奇怪为什么MySQL不允许我这样做.
I have tripped up on a curious SQL error. The last query doesn't work. Of course I can just split that DELETE into three queries, but I really wonder why MySQL doesn't let me do it this way.
一个小例子:
(SELECT id FROM stairs WHERE building = 123)
UNION
(SELECT id FROM lift WHERE building = 123)
UNION
(SELECT id FROM qrcodeid WHERE building = 123)
有效!
DELETE FROM startpoint WHERE id IN (SELECT id FROM stairs WHERE building = 123)
也可以!
而
DELETE FROM startpoint WHERE id IN (
(SELECT id FROM stairs WHERE building = 123)
UNION
(SELECT id FROM lift WHERE building = 123)
UNION
(SELECT id FROM qrcodeid WHERE building = 123)
)
引发错误
#1064-您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册,以获取正确的语法,以在第3行的"UNION(SELECT ID from lift WHERE building = 123)UNION(SELECT id FROM qrc)"附近使用
#1064 - 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 'UNION (SELECT id FROM lift WHERE building = 123) UNION (SELECT id FROM qrc' at line 3
有人知道吗?
推荐答案
尝试使用此版本:
DELETE FROM startpoint
WHERE id IN (select *
from ((SELECT id FROM stairs WHERE building = 123)
UNION
(SELECT id FROM lift WHERE building = 123)
UNION
(SELECT id FROM qrcodeid WHERE building = 123)
)
我认为该问题是带有子查询定义的奥秘问题.子查询是select
语句,而union
是select
语句的结合.
I think the issue is an arcane issue with the definition of a subquery. A subquery is a select
statement, whereas a union
is a conjunction of select
statements.
实际上,如果您想提高效率,则根本不会使用这种方法.我只是试图显示如何解决该错误.更好的解决方案是:
Actually, if you want efficiency, you wouldn't use this approach at all. I was just trying to show how to fix the error. A better solution would be:
DELETE sp FROM startpoint sp
WHERE EXISTS (select 1 from stairs s where s.building = 123 and s.id = sp.id) or
EXISTS (select 1 from lift l where l.building = 123 and l.id = sp.id) or
EXISTS (select 1 from qrcodeid q where q.building = 123 and q.id = sp.id);
建议在stairs(id, building)
,lift(id, building)
和qrcodeid(id, building)
上使用索引.
Indexes are recommended on stairs(id, building)
, lift(id, building)
, and qrcodeid(id, building)
.
这篇关于MySQL DELETE FROM与IN条件的UNION子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!