Deleting a row is coming up with error in SQL 2005 using Visual Studio

Question

Deleting a row is coming up with error in SQL 2005 using Visual Studio

OP_HELP

SSCrazy

Points: 2618
More actions
October 6, 2008 at 6:23 pm

#191888

If anyone could help me out with the following error message, it would be appreciated. I have a 756-row table and two of the rows are identical. When I try to delete the idential row, I get the following message:
A problem occurred attempted to delete row 2.
Error Source: Microsoft.VisualStudio.Datatools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows)
This table has non-unique indexes on only 3 columns. They're non-unique, so I do not understand the errors. I cannot find any documentation within Visual Studio to understand how to resolve this.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

If you add an Identity column with a name like RowID it will resolve the issue. You can also use this column as the Primary Key.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It's basically saying that you are asking for a single row to be deleted, but if it does the delete, two will go. To delete only one of duplicate set, you must have some way of identifying that row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

You can always use SET ROWCOUNT 1 and then do the delete. Don't forget to set the rowcount back to 0.

It does, however, show the fundamental flaw that others have already mentioned... no primary key.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OP_HELP SSCrazy Points: 2618 More actions · Answer 4

Ken, it works! Thanks. I set identity to "yes", made it a PK.

And thanks for the feedback for this answer from everyone else.

Ken Simmons (10/6/2008)
If you add an Identity column with a name like RowID it will resolve the issue. You can also use this column as the Primary Key.