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

Question

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

zoubida13 · Accepted Answer · 2019-10-29 10:58:07Z

29

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

edited Oct 29, 2019 at 10:58

answered May 16, 2016 at 10:28

zoubida13

1,77912 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mohsen.2 Over a year ago

when i try this code that you were answerd i get this message "[Err] 1248 - Every derived table must have its own alias "

mohsen.2 Over a year ago

thank you its work

fool4jesus Over a year ago

You're right, that is counter-intuitive, and you're also right, it worked like a charm. :-)

Ferdinand.kraft Over a year ago

Is this guaranteed to run atomically?

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related