MySQL DELETE FROM with UNION subquery by IN condition

Question

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.

A little example:

(SELECT id FROM stairs WHERE building = 123)
UNION
(SELECT id FROM lift WHERE building = 123)
UNION
(SELECT id FROM qrcodeid WHERE building = 123)

works!

DELETE FROM startpoint WHERE id IN (SELECT id FROM stairs WHERE building = 123)

works, too!

Whereas

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)
)

raises the error

#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

Anyone a clue?

Solution 1

Try this version instead:

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)
                )

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.

EDIT:

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);

Indexes are recommended on stairs(id, building), lift(id, building), and qrcodeid(id, building).

Solution 2

Try that 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)

Comments

[rpasianotto]

As you can see there aren't brackets!

[Nathan Stretch]

This works (once SQL errors are fixed - see my edit). It is not as efficient as rkpasia's solution, but it works for cases where parentheses on the union members are required, such as if LIMIT is used on the child queries.