MySQL DELETE FROM with subquery as condition

Question

I am trying to do a query like this:

DELETE FROM term_hierarchy AS th WHERE th.parent = 1015 AND th.tid IN (     SELECT DISTINCT(th1.tid)     FROM term_hierarchy AS th1     INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)     WHERE th1.parent = 1015 ); 

As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:

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 'AS th WHERE th.parent = 1015 AND th.tid IN (   SELECT DISTINCT(th1.tid)   FROM ter' at line 1 

I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what's wrong here?

Update: As answered below, MySQL does not allow the table you're deleting from be used in a subquery for the condition.

Answer 1

For others that find this question looking to delete while using a subquery, I leave you this example for outsmarting MySQL (even if some people seem to think it cannot be done):

DELETE e.* FROM tableE e WHERE id IN (SELECT id              FROM tableE              WHERE arg = 1 AND foo = 'bar'); 

will give you an error:

ERROR 1093 (HY000): You can't specify target table 'e' for update in FROM clause 

However this query:

DELETE e.* FROM tableE e WHERE id IN (SELECT id              FROM (SELECT id                    FROM tableE                    WHERE arg = 1 AND foo = 'bar') x); 

will work just fine:

Query OK, 1 row affected (3.91 sec) 

Wrap your subquery up in an additional subquery (here named x) and MySQL will happily do what you ask.