Parsu Parsu - 6 months ago 10
MySQL Question

Delete Duplicate Records in table using SQL

So I have a situation here where I need to delete duplicate records.
The Scenario here is I have 5 key fields.
4 fields are the actual data and 5th one is the auto generated unique number.
So when I check for counts using group by using only these 4 fields, I have duplicates of around 400 records. But unique number for these duplicate records are different(we can delete the record which has max value for unique number).
Is there a way to delete these duplicate records ?
Also these are 6 other non primary key fields but they are just for reference sake.

Say for example :

+----------+-------+---------+---------+-----------+
| Field | Field | Field 3 | Field 4 | Unique,Id |
| 1 | 2 | | | |
+----------+-------+---------+---------+-----------+
| Batman | 100 | 50 | Chicago | 100 |
+----------+-------+---------+---------+-----------+
| Batman | 100 | 50 | Chicago | 101 |
+----------+-------+---------+---------+-----------+
| Superman | 50 | 50 | Florida | 102 |
+----------+-------+---------+---------+-----------+
| Flash | 90 | 40 | London | 103 |
+----------+-------+---------+---------+-----------+
| Flash | 90 | 40 | London | 104 |
+----------+-------+---------+---------+-----------+


In this example, the first 4 fields are actual data fields.

So you can see Batman and flash have duplicate values(for first 4 fields) but the unique id is different. So here I need to delete one record from each so that they don't remain as duplicates. The maximum value of unique id for that duplicate record can be deleted. So 102 and 104 is the max unique id value for Batman and Flash and they should be deleted.

Please help me on this.

Answer

You can do it with a CTE.

WITH cte as (
select
 ROW_NUMBER() OVER ( PARTITION BY FIELD1 ORDER BY ID) FirstOcurrence,
*
from mySuperHeroTable 
)
delete from cte where FirstOcurrence <> 1

EDIT:

I would suggested that you add a Unique constraint on Field1 after you run the delete. This way you won't run into this situation again.

ALTER TABLE mySuperHeroTable ADD CONSTRAINT UQ_mySuperHeroTable_Field1 UNIQUE (Field1);
Comments