Using sql to find duplicate records and delete in same operation

Question

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.

Answer 1

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;