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?

Two options

Option #1

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

ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)

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

ALTER TABLE Table1
drop COLUMN TempID

But this adds a new column to the table which is not really the best option so on to option #2

Option #2

We can simply issue a simple delete command taking advantage of the Set RowCount command.

SET ROWCOUNT 1

DELETE FROM Table1
WHERE id= 1

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.