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, Mar 15 '12 at 17:14
possible duplicate of [How to delete Duplicates in MySQL table](http://stackoverflow.com/questions/672702/how-to-delete-duplicates-in-mysql-table) — Marc B, Mar 15 '12 at 17:14

D'Arcy Rittich · Answer 1 · 2012-03-15T17:20:58.447

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.

score 1 · Answer 2 · edited Aug 06 '12 at 10:30

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;

score 0 · Answer 3 · answered Mar 15 '12 at 17:19

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 '12 at 17:19

PoX

1,229
19
32

From the OP: "I'm able to display duplicates in my table" —the issue is deleting them. – D'Arcy Rittich Mar 15 '12 at 17:25
Well if you can select them you can delete them with using Delete ... where ... select – PoX Mar 16 '12 at 16:34

Mysql delete duplicates

3 Answers3