SQL command to remove duplicate records

Guys

I have one table name "address" with only one field "email" and I wish to remove all duplicate email addresses from this table.

Please help me and give me command to do this?

Thank you

GCS

 

This will only work if you have a unique id field

first creates a backup

SELECT email
FROM address a
WHERE id > ( SELECT min(id)
FROM address b
WHERE b.email = a.email );

Code (markup):

DELETE email
FROM address a
WHERE id > ( SELECT min(id)
FROM address b
WHERE b.email = a.email );

Code (markup):

 

You could also add a unique index on the email field so that you won't get duplicate addresses going forward.

 

In fact I imported around 10,000 email addresses from text file and now I wish to delete duplicates, right now I have only one field to simply process. Once duplicates are removed then I will add more fields.

GCS

 

deleting duplicates wont be easy for large amount of data coz u have to individually check each email and tally it with all the records ..

 

An easy way to do this is to create a new table and copy the distinct values (values without their duplicates) from the old table into a new one:

select distinct *[FONT=monospace]
[/FONT]into NewEmails[FONT=monospace]
[/FONT]from Emails

Code (markup):

This will remove all the duplicate emails in your list. Then you can go ahead and delete the table you currently have and use the new one you just copied the values into.