delete duplicate entry except one

[kunal4sd]

how can i delete duplicate entry except one from a table in mysql. please send that query to me.
thanks
kunal

[ronverdonk]

Remove all duplicate names from table doubles (create here just show content):

CREATE TABLE doubles (id INT(2) PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(20));
 
CREATE TABLE temp LIKE doubles;
INSERT INTO temp SELECT doubles.* FROM doubles;
DELETE FROM doubles WHERE id NOT IN (SELECT MIN(id) FROM temp GROUP BY name);
DROP TABLE temp; 

Ronald :cool:

[rubing]

I wrote a tutorial on how to remove similar records and duplicates from a table. It is located at www.weareallhumansnow.com

[amitpatel66]

Remove all duplicate names from table doubles (create here just show content):

CREATE TABLE doubles (id INT(2) PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(20));
 
CREATE TABLE temp LIKE doubles;
INSERT INTO temp SELECT doubles.* FROM doubles;
DELETE FROM doubles WHERE id NOT IN (SELECT MIN(id) FROM temp GROUP BY name);
DROP TABLE temp; 

Ronald :cool:

Slight modification from Ronald's code, to keep latest record and delete the older one:

DELETE FROM doubles WHERE id IN (SELECT MIN(id) FROM temp GROUP BY name);