Duplicate Query, automatically remove duplicate entries

[ssampier]

I have an existing duplicate query that compares two columns that are identical, PhoneNumber and EmailAddress. The query also displays the Primary Key, an autonumber field called AutoNum.

I have 10,316 results so I'd like to use an SQL statement to automatically remove these entries. Of course, this query contains entries I'd would like to keep. I'd like to specify to compare the AutoNum field to the duplicates (keep the smallest field, remove the remainder).

I can perform the SQL delete query myself, I'm just having difficulty specifying to ignore the smallest AutoNum field.

Here's an example:

AutoNum PhoneNumber EmailAddress
1 555-9999 bobsmith@isp.com
101 555-9999 bobsmith@isp.com
5 123-4567 george@isp.com
105 123-4567 george@isp.com
120 123-4567 george@isp.com
10 555-8888 smilingbob@isp.com
25 555-8888 smilingbob@isp.com
130 555-8888 smilingbob@isp.com

In this example: 101, 105, 120, 25, and 130 would be deleted. 1, 5, and 10 would remain.

 

[traingamer]

Try this to see if it gets the records to delete, then change it to a delete query

select tbl.*
from tbl 
where tbl.autonum not in (Select Min(t2.autonum) as MinId from tbl as t2 group by t2.autonum)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ssampier]

I run the query, it's really slow, and it returns 0 results. Let me know if I keyed it wrong:

SELECT [Find duplicates for tblEmail].*
FROM [Find duplicates for tblEmail]
WHERE [Find duplicates for tblEmail].UniqueIDNum NOT in (Select Min(t2.UniqueIDNum) as MinId from [Find duplicates for tblEmail] as t2 group by t2.UniqueIDNum)

I appreciate your help.

 

[traingamer]

Sorry, don't know what I was thinking. You need to group by phone and email in the nested query

...as t2 group by t2.[phone], t2.[email])

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ssampier]

It worked! Thank you very much. I am having a slight difficulty with the Delete query, however. I am trying to combine this query as a subquery into the main delete query. I don't have much experience with subqueries, so I might be going about this all wrong.

My faulty code (it gives me an error message):

DELETE UniqueIDNum, PhoneNumber, EmailAddress
FROM tblEmail 
WHERE tblEmail.UniqueIDNum IN (SELECT [Find duplicates for tblEmail].*
FROM [Find duplicates for tblEmail]
WHERE ((([Find duplicates for tblEmail].UniqueIDNum) Not In (Select Min(t2.UniqueIDNum) as MinId from [Find duplicates for tblEmail] as t2
GROUP BY
t2.[PhoneNumber], t2.[EmailAddress]))));

Again, I appreciate your help.

 

[PHV]

Why not simply this ?
DELETE FROM tblEmail
WHERE UniqueIDNum Not In (Select Min(UniqueIDNum) as MinId from [Find duplicates for tblEmail]
GROUP BY PhoneNumber, EmailAddress);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ssampier]

PHV, thanks for your help, that query worked, but it has some strange results. Namely, the query returns 6101 results. The previous query returned only 5145 results.