CODEWITHSUNDEEP
mysqlsql
Lusi
Lusi

Reputation: 411

mysql delete query with sub query not working

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?

Upvotes: 0

Views: 762

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

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    
)

Upvotes: 0

juergen d
juergen d

Reputation: 204924

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
)

Upvotes: 3

Related Questions