Delete-Statement with Union in subquery won't work

Question

I have a Query with Union-Statement where I retrieve all UserIds which are present in the Requester & Provider Column of the table Orders.

SELECT Requester
FROM Orders
UNION 
SELECT Provider
FROM Orders

This query yields some 7000 results in under a second.

Now i have table named 'Persons' where those UserIds are linked to the persons data. Using the above query i would like to clean this table and only keep the UserIds that are present in the result of the union-statement above.

So I made the following delete-statement:

DELETE FROM
Persons
WHERE
UserId NOT IN(

SELECT Requester
FROM Orders
UNION 
SELECT Provider
FROM Orders
)

Yet this query refuses to execute (even after 5 minutes waiting). What could be the reason for my query to fail? Am I not allowed to use UNION as Statement in the subquery here?

Note: following MySQL DELETE FROM with UNION subquery by IN condition didn't solve it for me.

What happens when you replace the 'delete from' with a 'select * from'? Another approach - store the result of the union query in a temporary table and use that in the 'delete' query. — Ryan Vincent, Oct 30 '14 at 10:33
Slow as hell. But yielding results. Could it be because i'm overextending the `In `statement? — User999999, Oct 30 '14 at 10:36

Gordon Linoff · Accepted Answer · 2014-10-30T11:08:57.933

The query works. It just takes a long time. Perhaps the following will be more efficient:

DELETE FROM Persons
    WHERE UserId NOT IN (SELECT DocAuthor FROM Document WHERE DocAuthors IS NOT NULL) AND
          UserId NOT IN (SELECT DocActualApprovers FROM Document WHERE DocActualApprovers IS NOT NULL);

Then, for performance, it would be best to have (at least) two indexes: Document(DocAuthor) and DocAuthor(DocActualApprovers). Note that your original query will delete nothing if either DocAuthor or DocActualApprovers is ever NULL. Presumably, this is not what you want; hence, the where clause.

EDIT:

You might find that not exists works faster:

DELETE FROM Persons p
    WHERE NOT EXISTS (SELECT DocAuthor FROM Document d WHERE d.DocAuthor = p.UserId) AND
          NOT EXISTS (SELECT DocActualApprovers FROM Document WHERE d.DocActualApprovers = p.UserId);

The same indexes would help here.

Thank you for point me to the ´Null´problem! Your query works but still slow. Somehow I feel like the `IN-statement`is hurting my performance — User999999, Oct 30 '14 at 10:39

Delete-Statement with Union in subquery won't work

1 Answers1