Mysql error 1093 - Can't specify target table for update in FROM clause

Mysql error 1093 - Can't specify target table for update in FROM clause

Q : 

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 excuting:

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?

: Solution :

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.

You can Also Try 

Solution 2 : 

The inner join in your subquery is unnecessary. It looks like you want to delete the entries in story_category where the category_id is not in the category table.
Instead of this:


DELETE FROM story_category WHERE category_id NOT IN (SELECT DISTINCTcategory.id FROM category INNER JOINstory_category ONcategory_id=category.id);


Do this:
DELETE FROM story_category WHERE category_id NOT IN (SELECT DISTINCTcategory.id FROM category);




Cheers !
Hope you got the Ans !
still having probs ? let me know by comments !

Know More About :
PHP Freelancing India