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
This entry was posted in Joy of T-SQL, T-SQL Tips & Tricks. Bookmark the permalink.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s