CODEWITHSUNDEEP
mysql
Roushan Choudhary
Roushan Choudhary

Reputation: 24

Error Code 1093 in DELETE statement

My query is:-

delete from api_data 
WHERE local_id NOT IN( SELECT MAX(local_id) 
                       FROM api_data 
                       GROUP BY local_id);

But i am getting error which says:

You can't specify target table 'api_data' for update in FROM clause.

Any Help?

Upvotes: 1

Views: 589

Answers (1)

juergen d
juergen d

Reputation: 204746

In MySQL you can't delete from the same table you are selecting from. But you can use another subquery to cover that

delete from api_data
WHERE local_id NOT IN
(
  select * from 
  (
    SELECT MAX(local_id) FROM api_data GROUP BY local_id
  ) tmp
);

Upvotes: 3

Related Questions