Trouble with DELETE subquery 1

[ssampier]

I have a query that pulls data from a duplicate query. I need to make this query a subquery of a delete query--delete entries that are duplicate in a table--deleting data from the table tblEmail. I tried writing the SQL myself, but it gives an error.

This is the original query code:

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]

This is my modified code incorporating the subquery (it gives 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]))));

I'd appreciate any help you can offer. I don't have much experience with sub-queries, so I may be going about this all wrong.

 

[lupins46]

You need to change:
SELECT [Find duplicates for tblEmail].*

so that it just selects the uniqueidnum

(I'm not saying that's the only thing as I haven't looked further)

 

[PHV]

And what about simply this ?
DELETE FROM tblEmail
WHERE UniqueIDNum Not In (SELECT Min(UniqueIDNum) 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]

lupins, that did the trick! Thank you.

PHV, I tried that before and it returned some strange results (it had 6,100 results when the other query had 5104 results).

I appreciate all your help. Tek Tips is Five Stars *****