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
I used a wizard today to create a dataset (.xsd) and when I ran my web application I had hundreds of warnings in my newly generated dataset telling me my code was not CLS-compliant. I checked out what CLS-compliant means and apparently this warning is new in VS 2005 and is used to check to see if your application is following CLS (Common Language Specifications). In other words the CLS are a set of rules used to check if the code in one language can be used in another language.
I checked Google for an answer and only found a work around that turned the functionality of the CLS checking off. I have been looking for another way to go about fixing this problem, but I haven’t found anything yet. There has to be a reason why the dataset generated (by VS 2005) caused all of these errors. Has anyone else gotten CLS warnings and fixed the problem without just turning off CLS checking?
Thanks Jeff for allowing me to join geekswithblogs.net. Since I'm new to the community I should start off by telling everybody a little about myself. I have been developing software professionally for a year and a half now and have a B.S. in Information Systems Management.
I am at a stage in my life that I'm sure many of you have been in at some point in time. I'm a relatively young programmer and want to do just about everything. My attention is diverted between several different pursuits and I'm not quite sure what turn my career will take. Right now I'm focused on trying to get my MCSD, learning how to program games in C#/ C++, dipping into new technologies like Atlas and, oh yeah, that 9-5 that pays the bills.
I look forward to sharing my experiences and endeavors with you as I navigate through the programming world.