MySQL DELETE FROM with subquery as condition

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 eWHERE 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 eWHERE 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.

The alias should be included after the DELETE keyword:

DELETE thFROM term_hierarchy AS thWHERE 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);

You cannot specify target table for delete.

A workaround

create table term_hierarchy_backup (tid int(10)); <- check data typeinsert into term_hierarchy_backup SELECT DISTINCT(th1.tid)FROM term_hierarchy AS th1INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent != 1015)WHERE th1.parent = 1015;DELETE FROM term_hierarchy AS thWHERE th.parent = 1015 AND th.tid IN (select tid from term_hierarchy_backup);