MySQL: What is the safest way to remove duplicate rows from a MySQL TABLE?

Question

I have a MySQL TABLE. It contains mailing addresses we get from a data feed. But there are no customer records for the mailing addresses, so I don't have an easy way to match a customer record as a key to see if it exists already in the master TABLE. So I've decided to have the new daily data feed added to the master TABLE and then remove duplicates.

What is the safest way to remove duplicates? Obviously, I want to ignore the ID column field. But how do I do this for the following fields:

company_name
contact_name
address1
address2
address3
city
state
zipcode
phone_number
email_address

What if I rebuild the MySQL TABLE to include ALTER TABLE with UNIQUE KEY, would that be safe? For example:

ALTER TABLE people ADD UNIQUE KEY (company_name,contact_name,address1,address2,address3,city,state,zipcode,phone_number,email_address)

Would the above safely prevent duplicated records from being INSERTed to begin with?

Thanks!

http://stackoverflow.com/questions/14816863/what-is-the-best-way-to-delete-duplicate-values-from-mysql-table try this — Stvdd, May 06 '13 at 09:10
@bksiA duplicate is a row that contains the fields/columns I mentioned that each contain the exact same data. — Edward, May 06 '13 at 09:12

score 0 · Answer 1 · answered May 06 '13 at 09:12

0

This is simplest Query you can use Choose Max or min based on your requirement.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Thanks

answered May 06 '13 at 09:12

Kevin_cl

369
2
8

Please look at my added example using ALTER TABLE people ADD UNIQUE KEY. I'd be interested to know what you think. Thanks! – Edward May 06 '13 at 09:23

Alok Nath · Answer 2 · 2013-10-23T06:52:05.080

DELETE a FROM test a LEFT JOIN ( SELECT MIN(id) AS id, company_name, contact_name, address1, address2, address3, city, state, zipcode, phone_number, email_address FROM test GROUP BY company_name, contact_name, address1, address2, address3, city, state, zipcode, phone_number, email_address ) b ON a.id = b.id AND a.company_name = b.company_name AND a.contact_name = b.contact_name AND a.address1 = b.address1 AND a.address2 = b.address2 AND a.address3 = b.address3 AND a.city = b.city AND a.state = b.state AND a.zipcode = b.zipcode AND a.phone_number = b.phone_number AND a.email_address = b.email_address WHERE b.id IS NULL

MySQL: What is the safest way to remove duplicate rows from a MySQL TABLE?

2 Answers2