The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

I came across an error today in SQL Server 2005.

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.

The reason I got this error was because I created a table for a data mapping application and in my haste I forgot to include a primary key. I checked my table today and found records that were duplicated and whenever I tried to delete or edit a row in SQL Management Studio this error showed up. So I knew I had to put a primary key in the database and in order to do that I had to take care of the duplication. Below are 3 different ways I found to fix this problem.

Solution 1
The first solution is for getting rid of several duplicated rows. This solution used multiple queries.

Step 1.
This query puts the duplicate keys in a separate table that this query creates.

Step 2.
This query creates another new table and just includes unique primary keys.

*Before step 3 check the HoldUps table for duplicates. If you have duplicates in that table refer to the microsoft link below.

Step 3.
This query deletes the duplicate rows from the original table.

Step 4.
This query inserts the unique rows from the Holdups table into the original table.

Step 5.
Delete the two new tables that the queries created and you’re finished.

Solution 2
This solution is used for instances where you just need to delete one duplicate row. Just use a delete statement like you would any time you delete a row. The only difference is the SET ROWCOUNT 1 makes it so that only 1 row gets deleted. Delete the 1 row and that takes care of the duplication.

Solution 3
This solution is a yet another way to approach this problem. In this approach you use the query below to create a new column that numbers your records 1, 2, 3, etc. This will get rid of the duplication and allow you to delete the records or update them as you need. After you fix the duplication problem, just remember to delete the column.

The microsoft link I found that led me to solution 1.
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

 
  • Josetta Caudill

    Thank you!!