Mysql delete duplicates

Question

I'm able to display duplicates in my table

table name reportingdetail and column name ReportingDetailID

SELECT DISTINCT ReportingDetailID from reportingdetail group by ReportingDetailID  HAVING count(ReportingDetailID) > 1;
+-------------------+
| ReportingDetailID |
+-------------------+
|         664602311 | 
+-------------------+
1 row in set (2.81 sec)

Dose anyone know how can I go about deleting duplicates and keep only one record?

I tired the following

SELECT * FROM reportingdetail USING reportingdetail, reportingdetail AS vtable  WHERE      (reportingdetailID > vtable.id)  AND (reportingdetail.reportingdetailID=reportingdetailID);

But it just deleted everything and kept single duplicates records!

can you show the table schema and what makes something duplicate vs what you want to keep? — kaj
– kaj, Commented Mar 15, 2012 at 17:14
possible duplicate of How to delete Duplicates in MySQL table — Marc B
– Marc B, Commented Mar 15, 2012 at 17:14

D'Arcy Rittich · Accepted Answer · 2012-03-15 17:20:58Z

The quickest way (that I know of) to remove duplicates in MySQL is by adding an index.

E.g., assuming reportingdetailID is going to be the PK for that table:

mysql> ALTER IGNORE TABLE reportingdetail 
    -> ADD PRIMARY KEY (reportingdetailID);

From the documentation:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

Adding this index will both remove duplicates and prevent any future duplicates from being inserted. If you do not want the latter behavior, just drop the index after creating it.

Tisho · Accepted Answer · 2012-08-06 10:30:14Z

The following MySQL commands will create a temporary table and populate it with all columns GROUPED by one column name (the column that has duplicates) and order them by the primary key ascending. The second command creates a real table from the temporary table. The third command drops the table that is being used and finally the last command renames the second temporary table to the current being used table name.

Thats a really fast solution. Here are the four commands:

CREATE TEMPORARY TABLE videos_temp AS SELECT * FROM videos GROUP by
    title ORDER BY videoid ASC;
CREATE TABLE videos_temp2 AS SELECT * FROM videos_temp;
DROP TABLE videos;
ALTER TABLE videos_temp2 RENAME videos;

aphodius · Accepted Answer · 2012-03-15 17:19:34Z

0

This should give you duplicate entries.

SELECT `ReportingDetailID`, COUNT(`ReportingDetailID`) AS Nummber_of_Occurrences FROM reportingdetail GROUP BY `ReportingDetailID` HAVING ( COUNT(`ReportingDetailID`) > 1 )

answered Mar 15, 2012 at 17:19

aphodius

1,24919 silver badges33 bronze badges

2 Comments

D'Arcy Rittich Over a year ago

From the OP: "I'm able to display duplicates in my table" —the issue is deleting them.

aphodius Over a year ago

Well if you can select them you can delete them with using Delete ... where ... select

Russell Fox · Accepted Answer · 2025-05-01 23:30:16Z

CTE method which can be used in MySQL or SQL Server:

with iDupes
as (
    SELECT yourID
        , COUNT(*) c 
    FROM yourTable GROUP BY yourId HAVING c > 1
    )
, dupeRank
as (
    SELECT yourPK
        , id
        -- DESC for most recent, ASC for first:
        , ROW_NUMBER() OVER(PARTITION BY i.id order by i.yourPK DESC) as rowNum
    from yourTable i 
        inner join iDupes id
            on i.id = id.id
    )
DELETE i
FROM yourTable i
    INNER JOIN dupeRank dr
        ON i.yourPK = dr.yourPK
WHERE dr.rowNum != 1;

Collectives™ on Stack Overflow

Mysql delete duplicates

4 Answers 4

Comments

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Linked

Related