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

User · Answer

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.

User · Answer

Isn t the  in  clause in the delete     where  extremely inefficient  if there are going to be a large number of values returned from the subquery   Not sure why you would not just inner  or right  join back against the original table from the subquery on the ID to delete  rather than us the  in  subquery      DELETE T FROM Target AS T RIGHT JOIN  full subquery already listed for the in   clause in answers above    AS TT ON  TT ID   T ID    And maybe it is answered in the  MySQL doesn t allow it   however  it is working fine for me PROVIDED I make sure to fully clarify what to delete  DELETE T FROM Target AS T    Delete with Join in MySQL clarifies the DELETE   JOIN issue

User · Answer

You need to refer to the alias again in the delete statement  like   DELETE th FROM term hierarchy AS th        As outlined here in MySQL docs

User · Answer

you can use the alias in this way on the delete statement  DELETE  th   FROM term hierarchy th INNER JOIN term hierarchy th2 ON  th1 tid   th2 tid AND th2 parent    1015  WHERE th parent   1015

User · Answer

If you want to do this with 2 queries  you can always do something similar to this   1  grab ids from the table with   SELECT group concat id  as csv result FROM your table WHERE whatever    test        Then copy result with mouse keyboard or programming language to XXX below   2  DELETE FROM your table WHERE id IN   XXX     Maybe you could do this in one query  but this is what I prefer

User · Answer

I approached this in a slightly different way and it worked for me   I needed to remove secure links from my table that referenced the conditions table where there were no longer any condition rows left  A housekeeping script basically  This gave me the error - You cannot specify target table for delete   So looking here for inspiration I came up with the below query and it works just fine  This is because it creates a temporary table sl1 that is used as the reference for the DELETE    DELETE FROM  secure links  WHERE  secure links   link id  IN                            SELECT                  sl1   link id               FROM                                    SELECT                        sl2   link id                    FROM                       secure links  AS  sl2                       LEFT JOIN  conditions  ON  conditions   job     sl2   job                    WHERE                        sl2   action     something  AND                       conditions   ref  IS NULL                    AS  sl1                  Works for me

User · Answer

The alias should be included after the DELETE keyword   DELETE th 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

User · Answer

You cannot specify target table for delete   A workaround  create table term hierarchy backup  tid int 10     lt - check data type  insert into term hierarchy backup  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   DELETE FROM term hierarchy AS th WHERE th parent   1015 AND th tid IN  select tid from term hierarchy backup

User · Answer

CodeReaper   BennyHill   It works as expected    However  I wonder the time complexity for having millions of rows in the table  Apparently  it took about 5ms to execute for having 5k records on a correctly indexed table    My Query   SET status    1  WHERE id IN       SELECT id     FROM         SELECT c2 id FROM clusters as c2       WHERE c2 assign to user id IS NOT NULL         AND c2 id NOT IN            SELECT c1 id FROM clusters AS c1            LEFT JOIN cluster flags as cf on c1 last flag id   cf id            LEFT JOIN flag types as ft on ft id   cf flag type id          WHERE ft slug    closed                     x      Or is there something we can improve on my query above

[mysql] MySQL DELETE FROM with subquery as condition

Examples related to mysql

Examples related to sql

Examples related to subquery

Examples related to sql-delete

Examples related to in-subquery