automatically remove double records

[nightowl]

I would like to trace double records in my table (which have all fields in common, table exists of 3 or 4 fields) and remove all but one of the table ... is that possible ?

[nnichols]

It is a two step process and requires that the table has a PRIMARY or other UNIQUE KEY. What is your current table structure?

You can also do it by setting the combination of all fields as a UNIQUE KEY so that the db rejects duplicate records in the first place.

[[deleted]]

It is indeed a two-step process, but doesn't require anything at all.

The idea is to create a new empty table with almost the same structure as the original table. The only difference is that the new table has some sort of unique constraint on those tables that together may not contain duplicates.
Then you just insert the records from the old table into the new table, and the new table will refuse all duplicates because of the unique index.

Then you can rename/drop the old table and rename the new table to the old tables's name.

remember to make backups, and don't drop the old table, rename it. When you are happy that the new table is ok, then you can drop the old table.