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.

**Attention**: Good answer at the bottom http://stackoverflow.com/a/4471359/956397 simply add the table alias after `DELETE t FROM table t ...` — PiTheNumber, Jul 24 '14 at 10:06

score 339 · Answer 1 · edited Oct 22 '13 at 13:42

339

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.

edited Oct 22 '13 at 13:42

Benny Hill

6,191
4
39
59

answered Oct 19 '12 at 07:42

CodeReaper

5,988
3
35
56

12

Took some time but I got it to work. Important: 1) The first table must be aliased as shown here with "e", 2) the "x" at the end is not a placeholder, it is the alias for the temp table produced by the subquery "(SELECT id FROM tableE WHERE arg = 1 AND foo = 'bar')". – Tilman Hausherr Mar 08 '13 at 11:28
4

Why does this work? This changes a lot for me, but moreover, it shouldn't work. It *does* work, but it shouldn't. – donatJ Apr 14 '14 at 21:53
1

unbelievable. this actually works! but you are not forced to alias the table with e... you can use any alias you want. – Andrew Starlike Apr 29 '14 at 13:37
1

@jakabadambalazs My reasoning when coming up with it, was that the subquery starting with "SELECT id" finishes and returns a list of ids and therefore releases the lock of the table you want to delete from. – CodeReaper Jul 09 '14 at 07:24
13

@jakabadambalazs: We can't use the same table (`e`) in a DELETE and in its sub-SELECT. We _can_, however use a sub-sub-SELECT to create a temporary table (`x`), and use _that_ for the sub-SELECT. – Steve Almond Oct 06 '14 at 09:41
@CodeReaper Correct me if I'm wrong, but do you think subquery failed because you did not put an alias on the first subquery? at line `WHERE arg = 1 AND foo = 'bar');` should be `WHERE arg = 1 AND foo = 'bar') 'alias letter'`; – Sauron Oct 24 '14 at 20:35
Steve Almond is correct. It creates a temp table. Run an explain plan on the delete statement and you'll see what I mean. http://dev.mysql.com/doc/refman/5.7/en/explain-output.html – txyoji Sep 20 '16 at 22:04
1

Incredible, it does work in my case.. even I don't know why – Terry Lin Feb 12 '17 at 17:31
Correct - it works because the derived table is executed before the parent query - therefore there's no conflict in reading the table – Edmunds22 Dec 12 '18 at 09:14
This is a brilliant solution... I was already starting to think along these lines and it saved me an hour or so trying to come up with it myself. Thank you so much! @donatJ - it is technically correct and indeed SHOULD work because the intermediate query DOES NOT SPECIFY A TABLE FROM WHICH TO SELECT THE VALUE, it just selects the id value from the subquery... therefore the original error no longer applies... – jrypkahauer Jan 06 '21 at 18:01
can some one say how to convert this to sqlalchemy python ? – Mahmoud Magdy Oct 04 '22 at 06:25
1

@MahmoudMagdy I think you would be better off asking that as "new" question, if you have not found an answer yet. – CodeReaper Dec 16 '22 at 10:26
Great solution, I used this just today and its so odd its within 2 sub queries. – carnini Apr 24 '23 at 20:47

score 49 · Answer 2 · edited Oct 03 '12 at 02:04

49

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

edited Oct 03 '12 at 02:04

ajreal

46,720
11
89
119

answered Dec 17 '10 at 14:24

James Wiseman

29,946
17
95
158

3

This is a good answer. Proper Aliasing will go a long way to solve problems similar to the original post. (like Mine.) – usumoio Dec 04 '13 at 21:02

score 44 · Accepted Answer · answered Dec 17 '10 at 14:23

44

You cannot specify target table for delete.

A workaround

create table term_hierarchy_backup (tid int(10)); <- 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);

answered Dec 17 '10 at 14:23

ajreal

46,720
11
89
119

we are both right - see his comment to my answer below. Alias syntax and logic were both issues :) – JNK Dec 17 '10 at 14:51
Yeah, seems deleting via subquery is not currently possible in MySQL – thanks for taking a look at it :) – mikl Dec 17 '10 at 14:51
doesn't the "DELETE FROM term_hierarchy AS th" in that last line have the same problem? I get a syntax error the same as the OP. – malhal Jan 24 '12 at 20:02
You should add Index to term_hierarchy_backup.tid. – Roman Newaza Jan 02 '13 at 02:43
2

I am able to verify that, that it is neither possible in 2019 on MariaDB 10.3.14 or MySQL Community Server 5.7.27 – alpham8 Aug 29 '19 at 06:50

score 13 · Answer 4 · edited Oct 03 '12 at 02:03

13

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.

edited Oct 03 '12 at 02:03

ajreal

46,720
11
89
119

answered Dec 17 '10 at 14:20

JNK

63,321
15
122
138

is not about alias, please check the OP again – ajreal Dec 17 '10 at 14:24
@ajreal - I did, and please notice the error begins at the alias definition, and MySQL documentation explicitly states you need to use the alias in the DELETE statement as well as the FROM clause. Thanks for the downvote, though. – JNK Dec 17 '10 at 14:25
simply do this `delete from your_table as t1 where t1.id in(select t2.id from your_table t2);` what did you get ? – ajreal Dec 17 '10 at 14:27
5

The documentation clearly states; `Currently, you cannot delete from a table and select from the same table in a subquery.` http://dev.mysql.com/doc/refman/5.5/en/delete.html – Björn Dec 17 '10 at 14:36
Ah, as Björn says, it is impossible :( Fixing the alias thing just gives a different error: "You can't specify target table 'th' for update in FROM clause" – mikl Dec 17 '10 at 14:49
@mikl - Then you had two errors :) Fix the alias and use ajreal's workaround. – JNK Dec 17 '10 at 14:50
2

you don't have to fix the alias, just don't specify target table for selecting in delete ...this is the real problem – ajreal Dec 17 '10 at 16:30

score 9 · Answer 5 · answered Aug 01 '14 at 11:12

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.

Duplicate of the one from @CodeReaper above... good call tho... ;) — jrypkahauer, Jan 06 '21 at 18:06

score 6 · Answer 6 · edited May 23 '17 at 11:47

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.

score 3 · Answer 7 · answered Dec 25 '17 at 16:05

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.

score 2 · Answer 8 · answered Oct 11 '19 at 02:17

2

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;

answered Oct 11 '19 at 02:17

YakovGdl35

331
3
4

score 0 · Answer 9 · answered Feb 11 '19 at 14:06

@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 DELETE FROM with subquery as condition

9 Answers9

Linked

Related