Hi,

I am importing a lot of emails into a MySQL database using a custom script I made. Anyway - I imported 4.6MB of email, and I think there might be some dupes in there.

What would be the best way to go about removing dupes from the database. I want to try and use a SQL query rather then a PHP one...

What have should have done was done a search for each email before I added them, and if it was already there not added it, but I was lazy and didn't...

Any advice as to what I should do? Thanks in advance.

Michael Bray

MySQL doesn't support nested selects in a DELETE.

You are best off using a small script to insert the records from the one table into a new table, as long as they don't exist.

If you don't think there are too many duplicates, you could try to the count function:

Something like:

SELECT ID,Email, COUNT(*) FROM Email_Table GROUP BY Email where COUNT(*)>1;

You could then manually delete the matching records, or feed them into a program to delete.

All in all it would probably have been easier to set the Email field to "unique" and just ignored an insert-error when inputting the records.






http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Huh - your right, I should have just set the field to unique :P

Thanks!

Michael Bray