enigma6205 enigma6205 - 6 months ago 17
SQL Question

Delete IDs that repeat more then once, but leave the first occurrence

I have a table and need to delete entire row where ID occurs second and subsequent times, but leave the first occurrence of suCustomerIDBy the way. M table has ID which is a primary key and CustometID which is duplicated. So I need to remove all rows with duplicated CustomerID.

Delete From Table1 where ID IN (select ID From Table1 where count(distinct CutomerID) >=2 group by CustomerID)


The code above will delete all id including the first occurrence of each of the IDs, but I need to keep their first occurrence. Please advise.

Answer

This code should give you what you need.

There may be better ways to do it if you can provide the full table schema for Table1

If you obtain the row number and then just ignore the first ones:

;WITH cte 
AS 
(
  SELECT ID, 
     ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS Rn
  FROM [Table1]
)
DELETE cte WHERE Rn > 1