I had a MySQL database that was automatically populated with raw data and no checking. So as a result, I had duplicate rows of data and each data row had a unique key id number (auto increment). The idea was to get rid of the duplicate rows. I was lucky that one of the fields in the row was an equipment serial number, and I used that fact to easily eliminate the duplicates.Lets say that the table was called equipment_table. I would copy the table to make equipment_table_clean with the following:
CREATE TABLE equipment_table_clean LIKE equipment_table;
We know that the the serial number is the thing that defines duplicates so:
ALTER TABLE equipment_table_clean ADD UNIQUE (serial_number);
Then feed the data into the new table with the following command:
INSERT IGNORE INTO equipment_table_clean (serial_number, name, col1, col2) SELECT serial_number, name, col1, col2 from equipment_table;
Works like magic and saves a lot of work. Then drop the old table and rename the new table (after you have checked the data).
