mySQL "delete duplicate records"

[Anon]

Does anyone know a script that will delete duplicate records in my mySQL database?

Searching through topics, I've found people saying, "the topic of deleting duplicates has already been discussed and answered..." but I have found that these so-called solutions do not work; they only give me error messages.

I use the latest version of mySQL, version 3.23.39.

Thank you

[Anon]

Here\'s what I do:

Say your table is called users with the fields id, fname, lname, address

create table temp as select distinct fname, lname, address from users;
(all fields but the id which will make them all distinct if included)

drop table users;

create table users as select * from temp;

alter table users add id int not null auto_increment primary key;

finished!!

[Anon]

warning...
before you drop any tables, either do a mysqldump and backup the data first or just make very sure that all the data that you need is in the temp table before dropping the users.

or before you do anything, just duplicate the table...

create table users_bak as select * from users;

Mark wrote:

Here\'s what I do:

Say your table is called users with the fields id, fname, lname, address

create table temp as select distinct fname, lname, address from users;
(all fields but the id which will make them all distinct if included)

drop table users;

create table users as select * from temp;

alter table users add id int not null auto_increment primary key;

finished!!