August 2010 (1)
I had a user ask me today how they can delete a duplicate row from their database table. They were receiving the following error message when they tried to delete the duplicate row.
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.
The reason that the user gets this message is because the table did not have a Primary Key or other Unique Index defined. Why, that’s another story and not one I know!
An example table
Some example data
Notice the duplicate row (row 1 and row 3)
So the user in this case would select the first row and hit the delete button
They would then see this message
So how do we get rid of the duplicate row?
Add a new column which contains unique values
We could run the following code to add the new column and populate the new column with unique values
After issuing this command we will have something like this.
The user can now select their row and hit delete because SQL Server can uniquely identify this row.
You could then remove the new column
But this adds a new column to the table which is not really the best option so on to option #2
We can simply issue a simple delete command taking advantage of the Set RowCount command.
What this does is limit the delete command to one record. Since the data is duplicated you do not care which row is deleted as long as only one of the rows is deleted. No adding and removing columns.