Sergio del Amo Sergio del Amo - 6 months ago 72
MySQL Question

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.

Read more