mysql delete query with sub query not working

Question

I try to create mysql event that should delete duplicate rows of table.

This is my query

DELETE FROM locations
WHERE id NOT IN (SELECT  id
                 FROM locations
                 GROUP BY acc_id,`date`)

I got this error:

Error Code: 1093
You can't specify target table 'locations' for update in FROM clause.

How can I change the query to make it work?

juergen d · Accepted Answer · 2013-10-03 06:44:31Z

3

In MySQL you can't delete from the same table you are selecting from. But you can trick MySQL with another subselect

DELETE FROM locations
WHERE id NOT IN 
(
  select * from 
  (
      SELECT id
      FROM locations
      GROUP BY acc_id, `date`
  ) x
)

answered Oct 3, 2013 at 6:44

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

Comments

M Khalid Junaid · Accepted Answer · 2013-10-03 06:44:56Z

0

Try to provide the custom alias in your subquery you cannot directly specify the same table in update/delete

DELETE FROM locations
WHERE id NOT IN (
SELECT new_table.id FROM (
SELECT  id
FROM locations
GROUP BY acc_id,`date`
) new_table    
)

answered Oct 3, 2013 at 6:44

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges