How to keep only one row of a table, removing duplicate rows?

Question

Hello

I have a table that has a lot of duplicates in the Name column. I'd like to only keep one row for each.

The following lists the duplicates, but I don't know how to delete the duplicates and just keep one:

SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;

Thank you.

Answer 1

It would probably be easier to select the unique ones into a new table, drop the old table, then rename the temp table to replace it.

#create a table with same schema as members
CREATE TABLE tmp (...);

#insert the unique records
INSERT INTO tmp SELECT * FROM members GROUP BY name;

#swap it in
RENAME TABLE members TO members_old, tmp TO members;

#drop the old one
DROP TABLE members_old;

Answer 2

See the following question: Deleting duplicate rows from a table.

The adapted accepted answer from there (which is my answer, so no "theft" here...):

You can do it in a simple way assuming you have a unique ID field: you can delete all records that are the same except for the ID, but don't have "the minimum ID" for their name.

Example query:

DELETE FROM members
WHERE ID NOT IN
(
    SELECT MIN(ID)
    FROM members
    GROUP BY name
)

In case you don't have a unique index, my recommendation is to simply add an auto-incremental unique index. Mainly because it's good design, but also because it will allow you to run the query above.

Answer 3

We have a huge database where deleting duplicates is part of the regular maintenance process. We use DISTINCT to select the unique records then write them into a TEMPORARY TABLE. After TRUNCATE we write back the TEMPORARY data into the TABLE.

That is one way of doing it and works as a STORED PROCEDURE.