You can't specify target table 'creature' for update in FROM clause

Question

I have a database containing creatures from a game.

2 tables:

"creature_template" containing all the data relative to the creatures

"creature" containing the information about the location of the creatures in game.

Here is the code:

    DELETE FROM 
     creature 
    WHERE 
     id not in 
    (SELECT DISTINCT 
     b.id 
    FROM 
     creature_template a inner join creature b on a.entry = b.id 
    WHERE 
     b.map != 571 
     AND a.`type` = 1 
     AND a.`type` = 10
    );

The type is the type of the creature (1 = beast, 10 = not specified)

What I want to do is to delete all the creatures that are on the map #571 and that are not beasts (type=1) or not specified (type=10).

The "map" column is in the "creature" table while the "type" column is in the "creature_template" table.

Each creatures in the game is identified in the database by an ID. The "creature" table has a column called "id" which contains these IDs while the equivalent in the "creature_template" table is a column called "entry".

So what I must do is to select all the creatures that are on the map #571, then exclude all the type 1 and 10 and delete the rest.

Problem is that when I execute the code above, I get the error saying "You can't specify target table 'creature' for update in FROM clause"

I did a couple of research here on stackoverflow and tried a few solutions I found.

...I ended up deleting all the data from the "creature" table. Luckily I got the data back from a backup I did yesterday.

Any idea how to solve my problem?

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Sep 05 '16 at 15:19

score 0 · Answer 1 · answered Sep 05 '16 at 15:03

0

Use "named subquery hack":

 DELETE FROM 
     creature 
    WHERE 
     id not in 
    (
     SELECT id FROM
      (
       SELECT DISTINCT b.id 
         FROM creature_template a inner join creature b on a.entry = b.id 
        WHERE b.map != 571 AND a.`type` = 1 AND a.`type` = 10
      ) A
    );

answered Sep 05 '16 at 15:03

Mike

1,985
1
8
14

Nope. I just tried your solution and it deleted all the data in the "creature" table. :/ Luckily I have a backup – NZoth Sep 05 '16 at 15:06
1

@NZoth Your condition is probably not chose any record. And `id not in()` select all for delete. Test any delete query as select, first. – Mike Sep 05 '16 at 15:11

score 0 · Answer 2 · edited May 23 '17 at 11:51

One possibility is to INSERT INTO a temp table using a SELECT that extracts from creature the rows you want to delete or the rows you want to keep (the temp table just needs to contain the PK columns), and in the WHERE of the subquery of your DELETE statement just use the temp table.

Just to show the concept, not tested:

SELECT DISTINCT 
     b.id
    INTO #TempRowsToKeep
    FROM 
     creature_template a inner join creature b on a.entry = b.id 
    WHERE 
     b.map != 571 
     AND (a.`type` = 1 or a.`type` = 10);

DELETE FROM 
     creature 
    WHERE 
     id not in 
      (SELECT id FROM #TempRowsToKeep);

DROP TABLE #TempRowsToKeep;

I'm no MySQl Expert, but if the solution from Mike's answer works I think you should go for that one, as it doesn't involve a temp table and it's only one statement instead of 3. The fact that when you tried it it deleted all your rows suggests that your condition in the innermost query didn't select any row, as he mentioned. In fact there is an error in it that certainly caused that:

AND a.`type` = 1 AND a.`type` = 10

will always be false. It should probably be

AND (a.`type` = 1 or a.`type` = 10)

My code above contains this correction, if you use it double-check it first.

Do you have an example of code for the "and in the WHERE of the subquery of your DELETE statement just use the temp table ." part? — NZoth, Sep 05 '16 at 16:34
@NZoth See my edit. Regardless, as a general suggestion, don't let your recovering of unwillingly deleted rows be "lucky"; either test the `DELETE` condition with a `SELECT` first as Mike's comment recommended, or systematically make a backup before each try. — SantiBailors, Sep 06 '16 at 07:21

You can't specify target table 'creature' for update in FROM clause

2 Answers2