CODEWITHSUNDEEP
mysqlsqlduplicatessql-delete
keruilin
keruilin

Reputation: 17512

Using sql to find duplicate records and delete in same operation

I'm using this SQL statement to find duplicate records:

  SELECT id, 
         user_id, 
         activity_type_id, 
         source_id, 
         source_type, 
         COUNT(*) AS cnt
    FROM activities
GROUP BY id, user_id, activity_type_id, source_id, source_type
  HAVING COUNT(*) > 1

However, I want to not only find, but delete in the same operation.

Upvotes: 2

Views: 511

Answers (1)

Alex Gitelman
Alex Gitelman

Reputation: 24722

delete from activities where id not in (select max(id) from activities group by ....)

Thanks to @OMG Ponies and his other post here is revised solution (but not exactly the same). I assumed here that it does not matter which specific rows are left undeleted. Also the assumption is that id is primary key.

In my example, I just set up one extra column name for testing but it can be easily extended to more columns via GROUP BY clause.

DELETE a FROM activities a 
   LEFT JOIN (SELECT MAX(id) AS id FROM activities GROUP BY name) uniqId 
   ON a.id=uniqId.id WHERE uniqId.id IS NULL;

Upvotes: 1

Related Questions