delete product group by name having count(*) > 1
  • 3
    sql-ex.ru/help/select17.php - msi
  • 3
    Possible duplicate issue: Remove duplicates from the database - Bald
  • @Bald there by the link is not sql server, judging by the FROM testing - PashaPash ♦
  • @PashaPash ran through the answers like it seems. Yes, and in my opinion, you can take the algorithm and apply - Bald
  • @Bald in SQL Server has native methods for finding the first row. and the ability to delete by results select without IN. - PashaPash ♦

2 answers 2

 WITH CTE AS ( SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk FROM product ) DELETE FROM CTE WHERE rnk > 1;

    The first simplest thing that comes to mind is to write data to a temporary table with a distink, then delete and re-insert it. Not at all optimal, but if the plate is small it will work quickly.

     select distinct name INTO #temp from product delete product group by name having count(*) > 1 insert into product select * from #temp drop table #temp