How to delete duplicate records via SQL.

[benmillus]

Is it possible to convert this to delete the duplicate records as they are found? If so how? If not what would you suggest?

SELECT FLD1, FLD2, FLD3, FLD4
FROM DED05
WHERE ((FLD1 In (SELECT FLD1 FROM DED05 As Tmp GROUP BY FLD1,FLD2 HAVING Count(*)>1 And FLD2 = FLD2)))
ORDER BY FLD1, FLD2;

 

[jdegeorge]

I can't find it right now, but I've seen a FAQ on this site about how to do this. Check the VBA forum for access. forum705

Jim DeGeorge

 

[benmillus]

Jim, Thanks for relying. I actually resolved this. The solution is:

DELETE TBL.*
FROM TBL
WHERE ((FLD1 In (SELECT FLD1 FROM TBL As Tmp GROUP BY FLD1,FLD2 HAVING Count(*)>1 And FLD2 = FLD2)));

BEN