Error Code 1093 in DELETE statement

Question

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?

Answer 1

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
);