delete duplicate email addresses

I have an email list that I need to delete duplicates from. How is this
done easily?
This query will tell me that I have duplicates:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) > 1)
This tells me how many clean addresses I have:
Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select email
from tblSelfTest Group By email Having Count(email) < 2)
How can I delete duplicates? Sometimese there are 3 or 4 of the same
email address in the file.William,
Does the Table have a unique number to identify the Email Addresses?
If so, you could use something like this..
delete from Table
where EmailID in (select max(EmailID)
from Table
group by Email
having (Count(Email)) > 1)
Thanks
Barry|||> I have an email list that I need to delete duplicates from. How is this
> done easily?
The easiest way is to SELECT DISTINCT into another table, and add a primary
key or unique index/constraint to that column so that this doesn't happen.
http://www.aspfaq.com/2431
http://www.aspfaq.com/2509|||First, make sure that the entire record is a duplicate and not just the
email. Otherwise, deleting records containing duplicate emails may result in
losing information. For example, if you have 2 records for John Doe with
same email but different phone numbers, then which record should you delete?
If it is just a list of emails, then you can select distinct from one table
into another table. For example:
select distinct email into TableB from TableA
If you do not want to deal with inserting into a 2nd table but rather
perform regular maintenance on the existing table, then you can delete out
duplicates using the method below. This requires that the table have a
unique key column. Test in the NorthWind sample database:
begin transaction
select count(*) from [Order Details]
delete
from
[Order Details]
where
OrderID <> (select max(X.OrderID) from [Order Details] as X where
X.ProductID = [Order Details].ProductID)
select count(*) from [Order Details]
rollback transaction
"William" <da@.northernit.net> wrote in message
news:NmHTd.40252$H05.29776@.twister.nyroc.rr.com...
> I have an email list that I need to delete duplicates from. How is this
> done easily?
> This query will tell me that I have duplicates:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) > 1)
> This tells me how many clean addresses I have:
> Select count(email) from tblTempAutoEmailerGrid1 Where email IN(Select
email
> from tblSelfTest Group By email Having Count(email) < 2)
> How can I delete duplicates? Sometimese there are 3 or 4 of the same
> email address in the file.
>
>|||Thanks. I did have an autonumber key field. The first method works well if
I execute the query several times to get rid of folks who have their emails
2-4 times in that database.
The addresses are collected when users take a diagnostics test on our
website. Duplicate come about because users find our content really useful.
They take the test or assessment quickly the first time and then 50% of the
time take the assessment again answering the questions with more thought.
We ask the persons age and usually the women lie about their age on the
first pass. When they find out that their results are compared to women
their age they take the test again usually answering the age question 2-5
years older (go figure...). We keep all of the data on every customer over
time. Lots of duplicats...
Anyway, thanks for the help. Worked great.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23a9Or90GFHA.2804@.TK2MSFTNGP10.phx.gbl...
> The easiest way is to SELECT DISTINCT into another table, and add a
primary
> key or unique index/constraint to that column so that this doesn't happen.
> http://www.aspfaq.com/2431
> http://www.aspfaq.com/2509
>