mohsen.2 mohsen.2 - 6 months ago 10
SQL Question

How to resolve MySQL error "You can't specify target table X for update in FROM clause"?

Why doesn't this query work?

DELETE FROM cancome WHERE user_id IN (
SELECT user_id FROM cancome
GROUP BY user_id
HAVING COUNT(user_id)>3
)
limit 3


I get this error message:


[Err] 1093 - You can't specify target table 'cancome' for update in FROM clause

Answer

The reason why this doesn't work is that MySQL doesn't allow you to reference the table that you are updating (cancome) within a subquery.

This can however be overcome by using a query instead of the table itself in the FROM, which has the effect of copying the requested table values instead of referencing the one that you are updating.

So effectively this, even if counter intuitive, will work :

DELETE FROM cancome WHERE user_id IN
 ( SELECT user_id FROM (SELECT * FROM cancome) AS cancomesub
 GROUP BY user_id HAVING COUNT(user_id)>3 )
 limit 3
Comments