0

I have to maintain an old MS SQL 2008 database with tons of records. I notice that about 90% are duplicates.

So what is the best way to remove only the duplicates.

I used... 

SELECT  [Col1],[Col2],[Col3],[Col...], COUNT(*) AS [DuplicateCount]
FROM myTable
GROUP BY  [Col1],[Col2],[Col3],[Col...]
HAVING COUNT(*) > 1;

to find the duplicates but I struggle how to delete all of them but not the first one. I found some hints with using TOP but not in combination with group by. Sorry but my SQL is too bad for that ;)

Thanks in advance!!!

GertR
  • 75
  • 1
  • 7
  • When I try: DELETE FROM [dbo].[Chargenverfolgung] LEFT OUTER JOIN ( SELECT MIN(ID) as RowId, [Col1],[Col2..] FROM [dbo].[myTable] GROUP BY [Col1],[Col2..] ) as KeepRows ON [dbo].[myTable].ID = KeepRows.RowId WHERE KeepRows.RowId IS NULL I get this error: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'LEFT'. Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'as'. – GertR May 23 '17 at 07:39
  • SELECT * instead of DELETE is working!?! – GertR May 23 '17 at 07:43

0 Answers0