Post reply

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

  • 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.

  • October 6, 2008 at 6:53 pm

    #881396

    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.

  • October 7, 2008 at 1:28 am

    #881462

    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

  • October 7, 2008 at 7:01 am

    #881608

    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)
    Intro to Tally Tables and Functions

  • October 8, 2008 at 6:44 pm

    #882653

    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.

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

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