Search code examples
mysqlsqlmysql-5.7

MySQL 5.7 remove duplicate rows in the same table based on multiple columns

I have a table with already existing records, I want to add a Unique constraint on multiple columns(app_instance_config_uuid, external_resource_id and spaceId), but first, I need to remove already existing duplicates. This is an example of the table I want to add the constraint.

enter image description here

The best solution i found is

DELETE FROM spaces_apps 
WHERE id IN ( SELECT id FROM ( SELECT MIN(id) AS id FROM spaces_apps 
GROUP BY spaceId, app_instance_config_uuid, external_resource_id 
HAVING COUNT(id) > 1 ) temp )

but the issue is that it only deletes one duplicate and if I need to delete more then one i need to run it again.

Important note that this is MySQL5.7 so using ROW_COUNT() and similar approaches doesn't work.

UPDATE: The first solution works even better when just changing IN to NOT IN and removing the HAVING clause! Thanks to @Pankaj for pointing this!

DELETE FROM spaces_apps 
    WHERE id IN ( SELECT id FROM ( SELECT MIN(id) AS id FROM spaces_apps 
    GROUP BY spaceId, app_instance_config_uuid, external_resource_id )temp )
Solution

  • I found solution for this. It's not the prettiest but it's the only one that works in my case.

    DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE 
    t1.created_at < t2.created_at AND
    t1.app_instance_config_uuid=t2.app_instance_config_uuid  AND
    t1.external_resource_id=t2.external_resource_id AND
    t1.spaceId=t2.spaceId;