How to delete duplicate records or rows among identical rows in a table where no primary key exists
It is a very common situation that you may find yourself
against a problem dealing with deletion of dublicate records in a database table.
This is a real problem if the records are identical even for values in each
column. So you can not distinguish one row from the other. This problem may
occur on any table if the table does not have a unique index or an identity
column. Unique indexes guarantees that columns within the index has no dublicate
values so each row in the table has also no dublicates. Identity columns are
columns whose numeric values are generated automatically in a sequential way. So
in a way using identity columns will also provide a uniqueness among the rows of
a table as well as in the identity column values.
But now we will deal with a situation where we can not avoid
dublicate records in a table and try to remove or delete the dublicate rows from
the database table.
There are three methods that we can get use of them in order to
delete dublicates in a table. One method is using the SET ROWCOUNT t-sql command.
And the second method uses the TOP tsql command. But if you open the SQL Server
2005 Books Online (BOL) you will see a note indicating that SET ROWCOUNT will
not affect DELETE, INSERT, and UPDATE statements in the next release of SQL
Server (probably in Katmai). So if you are writing your sql codes also for next
versions of SQL Server, then you should prefer choosing the second method and
use TOP in your codes. And the third method is adding an identity column to the
table to distinguish identical rows in the table. This is a small tricky method
:)
Before I explain both methods, it is better to create a table
that will help us simulate the problem.
CREATE TABLE Users
(
FirstName nvarchar(50),
LastName nvarchar(50)
)
GO
INSERT INTO Users (FirstName, LastName) VALUES (N'Eralper',N'Yilmaz')
INSERT INTO Users (FirstName, LastName) VALUES (N'Elvis',N'Presley')
INSERT INTO Users (FirstName, LastName) VALUES (N'Red',N'Kit')
INSERT INTO Users (FirstName, LastName) VALUES (N'Jane',N'Fonda')
INSERT INTO Users (FirstName, LastName) VALUES (N'Red',N'Kit')
INSERT INTO Users (FirstName, LastName) VALUES (N'James',N'White')
INSERT INTO Users (FirstName, LastName) VALUES (N'Alan',N'Black')
INSERT INTO Users (FirstName, LastName) VALUES (N'Elvis',N'Presley')
INSERT INTO Users (FirstName, LastName) VALUES (N'Jane',N'Fonda')
INSERT INTO Users (FirstName, LastName) VALUES (N'Elvis',N'Presley')
INSERT INTO Users (FirstName, LastName) VALUES (N'Jane',N'Fonda')
INSERT INTO Users (FirstName, LastName) VALUES (N'Elvis',N'Presley')
If you run a SELECT command on the table Users, the result set return from
the below sql command will be as shown in the table
SELECT* FROM Users
You will notice that in our sample database table some records have been
repeated two times, three times and even four times. Now we aim to get rid of
the dublicate rows in the Users table.
We can not succeed deleting dublicate records by simply running a DELETE FROM
sql statement like shown below
DELETE FROM Users WHERE FirstName = N'Elvis' AND LastName = N'Presley'
This will delete all the rows that have firstname value as Elvis and lastname
equal to Presley which means all of the four records will be deleted.
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
I guess, you have now a few techniques that you can use while dealing with
dublicate records or rows in your sql server databases.
Additional Tutorials for Dublicate Rows
If you have rows that differ from each other, you can use an other method to delete duplicate rows in a table.
For example if you have inserted date column and you want to keep the first rows inserted into the table by ordering due to the insert date column, you can use the ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) method.
You can find an other tutorial titled Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) to Delete Duplicate Rows in SQL Table among Kodyaz SQL articles for developers.
|