我犯了一个奇怪的SQL错误。最后一个查询不起作用。当然,我可以将DELETE拆分为三个查询,但是我真的很奇怪为什么MySQL不允许我这样做。
一个小例子:
(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"附近使用正确的语法
有人知道吗?
试试这个版本:
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
语句的合取。
实际上,如果你想要效率,你根本不会使用这种方法。我只是想展示如何修复这个错误。一个更好的解决方案是:
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)
进行索引。
试试SQL
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)