Find AND DELETE Duplicates

[MisterC]

Hi,
I have a query that finds duplicate records in my table. I need a query that will delete (just) the duplicates. Can anyone help me with the sql?
Here's what I have so far:

SELECT CKNumber, CKDate, CheckAmount 
FROM CKREGISTER
WHERE (((CKNumber) In (SELECT [CKNumber] FROM [CKREGISTER] As Tmp GROUP BY [CKNumber] HAVING Count(*)>1 )))
ORDER BY CKREGISTER.CKNumber;

Thanks for your help.

 

[coco86]

Try:

Delete *
FROM CKREGISTER
WHERE (((CKNumber) In (SELECT [CKNumber] FROM [CKREGISTER] As Tmp GROUP BY [CKNumber] HAVING Count(*)>1 )))

I strongly recommend backing up your table before you try this! You never know...

Also, you can preview the records that will be deleted before you actually run the query

 

[scriverb]

Give this SQL a try:

Delete A.CKNumber
FROM CKREGISTER as A
WHERE (((A.CKNumber) In (SELECT [CKNumber] FROM [CKREGISTER] As Tmp GROUP BY [CKNumber] HAVING Count(*)>1 )))
ORDER BY CKREGISTER.CKNumber;

Let me know if you need more help.
Bob Scriver