t-sql: how to delete duplicate records

Method 1: SET ROWCOUNT


Syntax for SET ROWCOUNT is as follows:

SET ROWCOUNT { number | @number_var }
SET ROWCOUNT limits the sql server engine to process a specific number of rows. So the process stops after the defined number of rows are reached. The default value for ROWCOUNT is 0 which means no limit on the returning result set so all rows are returned. After a ROWCOUNT command is run and all processes are completed, you can also set the ROWCOUNT to 0 to turn off this option.

If we return back to our sample, in order to delete four times repeated rows, we can set the ROWCOUNT value to 3

SET ROWCOUNT 3
DELETE FROM Users WHERE FirstName = N'Elvis' AND LastName = N'Presley'
SET ROWCOUNT 0
-- (3 row(s) affected)
After running the above DELETE FROM command with SET ROWCOUNT statement, the last status of the table Users as below.



We are successfull to delete the identical rows with the row number 9 in the above picture.

If you want to delete the identical records automatically we can use a cursor. It is important that while you are declaring the cursor for dublicate rows, you should select the count of identical rows minus one, since we want one of the dublicated records exist in the table after delete processes.

You should also pay attention to the SET ROWCOUNT commands around the DELETE FROM command in the body of the cursor.

DECLARE @Count int
DECLARE @FirstName nvarchar(50)
DECLARE @LastName nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT FirstName, LastName, Count(*) - 1
FROM Users
GROUP BY FirstName, LastName
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM Users WHERE FirstName = @FirstName AND LastName = @LastName
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Method 2: TOP


A second method we can use while removing dublicate records from Users table is using the TOP expression in DELETE statement. With the release of SQL Server 2005, as an T-SQL enhancement TOP expression now takes a variable where else in SQL Server 2000 TOP was expecting a constant numeric value. This is very useful since if we decide to use a cursor, etc to delete all dublicates once, we may use a variable with the TOP expression.

If we return back to our original sample data in the Users table, we can run a similar command to remove two of the three identical records having firstname equal to Jane and last name equal to Fonda

DELETE TOP (2) FROM Users
WHERE FirstName = N'Jane' AND LastName = N'Fonda'
If you have not used the (n) syntax, you shoul get the following error; don't worry, just use the paranthesis.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2'.
You can guess that we can use this DELETE TOP combination within a cursor in order to remove all the dublicated records leaving only one of them in the sample table. Here is the cursor that we can use:

DECLARE @Count int
DECLARE @FirstName nvarchar(50)
DECLARE @LastName nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT FirstName, LastName, Count(*) - 1
FROM Users
GROUP BY FirstName, LastName
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@Count) FROM Users WHERE FirstName = @FirstName AND LastName = @LastName

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor
Again I want to point to the issue that ROWCOUNT will not be considered in the next releases of SQL SERVER. You can find this information in the BOL on topics about ROWCOUNT and TOP. You can refer to ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/50705caa-4d0a-490f-92a4-75ece96e0a54.htm for more information. I also copied the important note from BOL to here:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that DELETE, INSERT, and UPDATE statements that currently are using SET ROWCOUNT be rewritten to useTOP.


Method 3: Adding an IDENTITY column


A third method can be adding an identity column to the table in order to distinguish all rows from each other. Of course, if you do not have the permissions to alter the table in order to add a new column, this method can not be implemented.

Run the below sql command to add a new column to the Users table, which will also fill the newly added column Id with integer values in sequential order and will distinguish each record from its identical ones.

ALTER TABLE Users ADD Id int IDENTITY(1,1)
After you run the above command, Users table will be as below:



Now it is easy to delete dublicates by using the Id column. For example, for dublicates of row with Id 8, we can run the below command

DELETE FROM Users WHERE Id IN (2,10,12)
And now let's look how we can automatically delete dublicates in this situation. We can use a Common Table Expression (CTE) to make a change and use a CTE instead of using a cursor. Here is the CTE (common table expression) sample that will delete the dublicates in our Users table.

WITH Dublicates_CTE(FirstName, LastName, Id)
AS
(
SELECT FirstName, LastName, Min(Id) Id
FROM Users
GROUP BY FirstName, LastName
HAVING Count(*) > 1
)
DELETE FROM Users
WHERE Id IN (
SELECT Users.Id
FROM Users
INNER JOIN Dublicates_CTE
ON Users.FirstName = Dublicates_CTE.FirstName
AND Users.LastName = Dublicates_CTE.LastName
AND Users.Id <> Dublicates_CTE.Id
)
It is important to note that CTEs (Common Table Expressions) are also new enhancements in t-sql with the new release of SQL Server, SQL Server 2005. So the above sql statement will not run on SQL Server 2000 databases. For more samples and definition on CTEs you can read the article titled Common Table Expression.

After you have done, you can drop the identity column Id by running an ALTER TABLE command as shown below:

ALTER TABLE Users DROP COLUMN Id

http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx