PHP: Simple way to delete duplicate rows in MYSQL DB?

A

Andy

New member
I have a database of around 50,000 rows. The columns are first, last, company, and email. I will have many records with the same first and company, or first and last, or last and company, but I want to delete all of the records that have the same in every category. Is there any simple way to do this? (I have phpMyAdmin)
 
Here's an example I worked out. It doesn't require PHP, only direct mySQL commands you can feed into phpMyAdmin:

-- create a test table to demonstrate the solution
CREATE TABLE addresses (
fname VARCHAR(32),
lname VARCHAR(32),
email VARCHAR(32)
);

-- populate the table with a few duplicate values
INSERT INTO addresses VALUES("John", "Smith", "[email protected]");
INSERT INTO addresses VALUES("John", "Smith", "[email protected]");
INSERT INTO addresses VALUES("John", "Smith", "[email protected]");
INSERT INTO addresses VALUES("Joe", "Smith", "[email protected]");
INSERT INTO addresses VALUES("Joe", "Smith", "[email protected]");
INSERT INTO addresses VALUES("Jim", "Smith", "[email protected]");

-- create a temporary table for the unique values
CREATE TEMPORARY TABLE temp LIKE addresses;
INSERT INTO temp(SELECT DISTINCT * FROM addresses);

-- clear the original table and load the unique values there
DELETE FROM addresses;
INSERT INTO addresses(SELECT DISTINCT * FROM temp);
DROP TABLE temp;
 
Back
Top