DELETE entire row with duplicate

[sal21]

I use this sql to select duplicate in field...
tow question:

- is this correct? (for test i use a select statement)
- if yes, how to modify to delete duplicates row?

Code: Select all

SELECT PORTAFOGLI.COD_PTF AS Espr1
FROM PORTAFOGLI
WHERE (((PORTAFOGLI.COD_PTF) In (SELECT [COD_PTF] FROM [PORTAFOGLI] As Tmp GROUP BY [COD_PTF] HAVING Count(*)>1 )))
ORDER BY PORTAFOGLI.COD_PTF;

[HansV]

If you want to delete duplicates, you will have to specify which of the duplicate records you want to delete; this can be difficult to do in a query.

It might be easier to create a new table with the unique values.

[kwvh]

I too have a similar issue. A table with 580,000 records and 25,000 duplicates. If I take the approach of creating a new table, how do I control which ones get added to the new table and which ones don't? By sorting the records in the append query?

[HansV]

Does your table contain Memo, Hyperlink or OLE fields? If not, you could do the following:

1) Copy the table and paste it with the structure only option, to create an empty table with the same design.
2) Create a query based on the (filled) table that returns all fields, with the Unique Values property set to Yes, and change it to an append query that outputs to the new empty table.
3) Run the append query.

[kwvh]

Hans,

Thanks! My plan is to create the new table and create indices to prevent duplications in the future. If I set up the new table and then append the old to it, won't that eliminate the dups as well?

Thanks again for the rapid response.

Ken

[HansV]

Setting unique indexes should work too.