Search code examples
sqlsql-serverduplicatesdelete-row

Delete duplicates but keep 1 with multiple column key

I have the following SQL select. How can I convert it to a delete statement so it keeps 1 of the rows but deletes the duplicate? 

select s.ForsNr, t.* 
from [testDeleteDublicates] s
join (
    select ForsNr, period,  count(*) as qty
    from [testDeleteDublicates]
    group by ForsNr, period 
    having count(*) > 1
) t on s.ForsNr = t.ForsNr and s.Period = t.Period
Solution

  • Try using following:

    Method 1:

    DELETE FROM Mytable WHERE RowID NOT IN (SELECT MIN(RowID) FROM Mytable GROUP BY Col1,Col2,Col3)

    Method 2:

    ;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY ForsNr, period 
                                       ORDER BY ( SELECT 0)) RN
         FROM   testDeleteDublicates)
DELETE FROM cte
WHERE  RN > 1

    Hope this helps!

    NOTE: Please change the table & column names according to your need!