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.
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;