Delete duplicate rows in sql server

Fist way:

CREATE TABLE users
     (
      username varchar(15),
      pwd varchar(15)
     ) ;
-----
DECLARE @username varchar(15)
DECLARE @cnt int

DECLARE cur CURSOR READ_ONLY
FOR
SELECT username
     , count(*) -1 as cnt
     FROM users
        GROUP BY username 
            HAVING Count(*) > 1
OPEN cur
FETCH cur 
    INTO @username
       , @cnt

WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE TOP(@cnt) 
             FROM users 
                WHERE username = @username
FETCH cur  
    INTO @username
       , @cnt
END
CLOSE cur
DEALLOCATE cur

Second way from sqlauthority.com:


DELETE
     FROM MyTable
        WHERE ID NOT IN ( SELECT MAX(ID)
                               FROM MyTable
                                  GROUP BY DuplicateColumn1 
                                         , DuplicateColumn2
                                         , DuplicateColumn3)
Advertisements