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;
id as the SELECT suggests, then he has duplicated ids, and this won't remove them.
You can't specify target table 'activities' for update in FROM clause
idfield really repeated, as your GROUP BY suggests?