Categories
mysql mysql-error-1093 sql-delete subquery

MySQL Error 1093 – Can’t specify target table for update in FROM clause

762

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries:

SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);

I tried to delete them executing:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);

But I get the next error:

#1093 – You can’t specify target table ‘story_category’ for update in FROM clause

How can I overcome this?

3

896

Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP’s exact query, please see other answers to this question

In MySQL, you can’t modify the same table which you use in the SELECT part.
This behaviour is documented at:
http://dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Alternatively, try nesting the subquery deeper into a from clause …

If you absolutely need the subquery, there’s a workaround, but it’s
ugly for several reasons, including performance:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary
table
, so it doesn’t count as the same table you’re updating.

… but watch out for the query optimiser

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn’t recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch="derived_merge=off";

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

7

  • 1

    Upvoted this answer because I had to delete items and could not get info from another table, had to subquery from same table. Since this is what pops up on top while googling for the error I got this would be the best fit answer for me and a lot of people trying to update while subquerieing from the same table.

    – HMR

    Dec 12, 2012 at 3:36

  • 2

    @Cheekysoft, Why not save the values into variables instead?

    – Pacerier

    Feb 24, 2015 at 3:47


  • 1

    @PeterV.Mørch Following mysqlserverteam.com/derived-tables-in-mysql-5-7, in case certain operations are carried out, merging cannot happen. E.g. provide the derived dummy table with a LIMIT (to inifity) and the error will never occur. That’s pretty hackish though and there is still the risk that future versions of MySQL will support merging queries with LIMIT after all.

    Aug 18, 2016 at 18:59

  • 1

    Can you, please, provide a full example about this workaround? UPDATE tbl SET col = ( SELECT … FROM (SELECT…. FROM) AS x); i’m still getting errors

    Sep 10, 2018 at 9:56


  • INNER JOIN Work for me 🙂

    Jan 30, 2020 at 11:16

472

NexusRex provided a very good solution for deleting with join from the same table.

If you do this:

DELETE FROM story_category
WHERE category_id NOT IN (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
)

you are going to get an error.

But if you wrap the condition in one more select:

DELETE FROM story_category
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
    ) AS c
)

it would do the right thing!!

Explanation: The query optimizer does a derived merge optimization for the first query (which causes it to fail with the error), but the second query doesn’t qualify for the derived merge optimization. Hence the optimizer is forced to execute the subquery first.

14

  • 10

    Maybe it’s because I’m in a bind today, but this was the easiest answer, even if it’s maybe not the “best” one.

    – Tyler V.

    Sep 5, 2014 at 23:13

  • 4

    This worked great, thanks! So what’s the logic here? If it’s nested one more level then it will be executed before the outer part? And if it’s not nested then mySQL tries to run it after the delete has a lock on the table?

    – Flat Cat

    Dec 14, 2014 at 16:40

  • 76

    This error and solution make no logical sense…but it works. Sometimes I wonder what drugs the MySQL devs are on…

    – Cerin

    Dec 16, 2015 at 17:01

  • 2

    Agree with @Cerin.. this is completely absurd, but it works.

    – FastTrack

    Jun 28, 2017 at 15:44

  • @ekonoval Thanks you for the solution but It does not make the minimum sense for me, looks like you is fooling MySQL and he accept it, lol

    – deFreitas

    Jul 24, 2017 at 16:59


115

Recently i had to update records in the same table i did it like below:

UPDATE skills AS s, (SELECT id  FROM skills WHERE type="Programming") AS p
SET s.type="Development" 
WHERE s.id = p.id;

8

  • 11

    Can’t this just be written as UPDATE skills SET type='Development' WHERE type='Programming'; ? This doesn’t seem to be answering the original question.

    – lilbyrdie

    May 30, 2014 at 15:23


  • 2

    Seems like overkill, @lilbyrdie is correct – it could only be UPDATE skills SET type='Development' WHERE type='Programming';. I do not understand why so many people is not thinking about what they do…

    – shadyyx

    Dec 5, 2014 at 10:00

  • 4

    this is the best answer here, IMHO. It’s syntax is easy to understand, you can re-use your previous statement and it’s not limited to some super specific case.

    Sep 7, 2015 at 14:28

  • 6

    Even if the example case is questionable, the principle is the best to understand and deploy in real-world scenarios. The shown query works because implicit join in table list creates a temporary table for a subquery, thus providing stable results and avoiding clash between retrieval and modification of the same table.

    – AnrDaemon

    Apr 26, 2017 at 13:11

  • 3

    regardless of what anyone might say about this being overkill, it still answers the question in terms of the title. The error which the OP mentioned is also encountered when a update is attempted using the same table in a nested query

    – smac89

    Jan 9, 2018 at 23:40