How to DELETE duplicate records in sql [duplicate]

Question

Possible Duplicate:
How to delete duplicate rows with SQL?

I have a table with records and I want to delete all duplicate records

DELETE FROM 'table'
WHERE 'field' IN 
(
SELECT 'field' FROM 'table' GROUP BY 'field'
HAVING (COUNT('field')>1)
)

Why isn't this working?

Answer 1

Maybe you can explore with the command DISTINCT to select only unique records based in a field.

You can create a new table with the unique entries based. As an example...

CREATE TABLE nonDuplicates  
SELECT DISTINCT * FROM yourTable group by field

Answer 2

This gives you more than one result:-

SELECT field FROM `table`
GROUP BY field
HAVING (COUNT('field)>1

Try to chenge this by:

SELECT TOP 1 field
FROM `table`
GROUP BY field 
HAVING (COUNT(field)>1