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?

Answer : 

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 DanDarc's answer
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
You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause.
Below is from Baron Schwartz, published at Nabble:
However, you can do multi-table updates like this:
UPDATE tbl AS a
  INNER JOIN tbl AS b ON ....
  SET a.col = b.col
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.





TAGS : | | |