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.
SELECT col1, col2, col3=COUNT(*)
INTO HOLDKEY
FROM Table1
GROUP BY col1, col2
HAVING COUNT(*) > 1
Step 2.
This query creates another new table and just includes unique primary keys.
SELECT DISTINCT Table1.*
INTO HoldUps
FROM Table1, HoldKey
WHERE Table1.col1 = HoldKey.col1
AND Table1.col2 = HoldKey.col2
*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.
DELETE Table1
FROM Table1, HoldKey
WHERE Table1.col1 = HoldKey.col1
AND Table1.col2 = HoldKey.col2
Step 4.
This query inserts the unique rows from the Holdups table into the original table.
INSERT Table1 SELECT * FROM Holdups
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.
SET ROWCOUNT 1
DELETE FROM Table1
WHERE col1 = ‘0001’
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.
ALTER TABLE Table1
ADD TempID int IDENTITY(1, 1)
The microsoft link I found that led me to solution 1.
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444