通过 IN 条件使用 UNION 子查询的 MySQL DELETE FROM

2022-01-23 00:00:00 union subquery mysql sql-delete in-subquery

我遇到了一个奇怪的 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).

相关文章