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?

Duplicate : http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table — crafter, Oct 03 '13 at 06:53

score 3 · Accepted Answer · answered Oct 03 '13 at 06:44

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 03 '13 at 06:44

juergen d

score 0 · Answer 2 · answered Oct 03 '13 at 06:44

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 03 '13 at 06:44

M Khalid Junaid

2 Answers2