Deleting duplicate rows when there is no primary key
Problem
Every once in awhile a table gets created without a primary key and duplicate records get entered. The problem gets even worse when you have two identical rows in the table and there is no way to distinguish between the two rows. So how do you delete the duplicate record?
Solution
One option that SQL Server gives you is the ability to set ROWCOUNT which limits the numbers of records affected by a command. The default value is 0 which means all records, but this value can be set prior to running a command. So let's create a table and add 4 records with one duplicate record.
Create a table called duplicateTest and add 4 records.
Expand|Select|Wrap|Line Numbers
- CREATE TABLE dbo.duplicateTest
- (
- [ID] [int] ,
- [FirstName] [varchar](25),
- [LastName] [varchar](25)
- ) ON [PRIMARY]
- INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
- INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')
- INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')
- INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')
Expand|Select|Wrap|Line Numbers
- SELECT * FROM dbo.duplicateTest
Expand|Select|Wrap|Line Numbers
- ID FirstName LastName
- 1 Bob Smith
- 2 Dave Jones
- 3 Karen White
- 1 Bob Smith
Expand|Select|Wrap|Line Numbers
- SELECT * FROM dbo.duplicateTest WHERE ID = 1 AND FirstName = 'Bob' AND LastName = 'Smith'
Expand|Select|Wrap|Line Numbers
- ID FirstName LastName
- 1 Bob Smith
- 1 Bob Smith
Expand|Select|Wrap|Line Numbers
- SELECT * FROM dbo.duplicateTest
- SET ROWCOUNT 1
- DELETE FROM dbo.duplicateTest WHERE ID = 1
- SET ROWCOUNT 0
- SELECT * FROM dbo.duplicateTest
Expand|Select|Wrap|Line Numbers
- SELECT * FROM dbo.duplicateTest
- DELETE TOP(1) FROM dbo.duplicateTest WHERE ID = 1
- SELECT * FROM dbo.duplicateTest
Here is one note from Microsoft about using SET ROWCOUNT:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.
Next Steps
Take a look how the ROWCOUNT command can be used to affect the results of your query
Also take a look at the TOP command and changes that have been implemented with SQL Server 2005
Start using TOP instead of ROWCOUNT for SQL Server 2005 and later
Hope this will be useful.
Best Regards,
Bangaar