Friday, September 7, 2012

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


Reactions:

0 comments:

Post a Comment

Any Questions or Suggestions ?

About

Professional & Experienced Freelance Developer From India, Technologist, Software Engineer, internet marketer and Open Sources Developer with experience in Finance, Telecoms and the Media. Contact Me for freelancing projects.

Enter your email address:

Delivered by FeedBurner

PHP Freelancer India - Google+