Skip to main content

Peter Marshall

remove duplicates in sql server

1 min read

Find your duplicates

  SELECT    [col1], [col2], COUNT(*)
  FROM      [db].[mstar].[table1]
  GROUP BY  [col1], [col2]
  HAVING    COUNT(*) > 1
THEN
begin transaction
SET NOCOUNT ON
SET ROWCOUNT 1
while 1 = 1
 BEGIN
   DELETE  
   FROM [mstar].[InvalidFundShareClassAttribute]
   WHERE [FundShareClassId]+ [CustomState] IN
        (SELECT  [FundShareClassId]+ [CustomState]
         FROM    [mstar].[InvalidFundShareClassAttribute]
         GROUP BY [FundShareClassId]+ [CustomState]
         HAVING  COUNT(*) > 1)
      IF @@Rowcount = 0
      BREAK ;
end;
 SET ROWCOUNT 0
 rollback;
or commit;

 

There is another way

;WITH CTE AS 
(
    SELECT EmpName, ROW_NUMBER() OVER 
    (
        PARTITION BY EmpName ORDER BY EmpID
    ) RowNumber
    FROM  dbo.Employee
)
SELECT *FROM CTE WHERE RowNumber > 1
Then
;WITH CTE AS 
(
SELECT EmpName, ROW_NUMBER() OVER 
(
    PARTITION BY EmpName ORDER BY EmpID
) RowNumber
FROM  dbo.Employee
)
DELETE FROM CTE WHERE RowNumber > 1