MySQL Error 1093 - Can't specify target table for update in FROM clause

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?

 Answers

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

---

The inner join in your sub-query is unnecessary. It looks like you want to delete the entries in story_category where the category_id is not in the category table.

Do this:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

Instead of that:

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

---

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
)

---

If you can't do

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);

because it is the same table, you can trick and do :

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[update or delete or whatever]

---

According to the Mysql UPDATE Syntax , it says right at the bottom.

Currently, you cannot update a table and select from the same table in a subquery.

I guess you are deleting from store_category while still selecting from it in the union.

---

Try to save result of Select statement in separate variable and then use that for delete query.

---

The simplest way to do this is use a table alias when you are referring parent query table inside the sub query.

Example :

insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

Change it to:

insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));