Post reply

if update fails on one row the how to ignore it and proceed with other step

  • November 2, 2011 at 2:28 am

    #262189

    hi,

    i have an update statement with multiple joins which is updating say 100 rows.

    If the whole command is failing due to corrupt data in one of the rows , then what is the simplest way to ignore and proceed with the remaining 99 updates?

    Thanks in Advance.:-)

  • November 2, 2011 at 2:35 am

    #1402003

    please let me know if using try,catch can help..

  • November 2, 2011 at 2:40 am

    #1402004

    Depends how do u define failure here... a failure is , say, datatype mismatch or failure to find a matching row ?

  • November 2, 2011 at 2:42 am

    #1402007

    If you've got corrupt data in your database then I would recommend that you deal with that before anything else. Have a read about DBCC CHECKDB if you haven't used it before.

    John

  • November 2, 2011 at 2:43 am

    #1402009

    If that's a single statement update, then it must succeed or fail as a single operation, not partially succeed and partially fail. That's required by the ACID rules of relational databases.

    What's the error and what's the statement?

    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

  • November 2, 2011 at 3:23 am

    #1402019

    if error occurs it rollbacks right?

    ===============================================================

    "lets do amazing" our company motto..

  • November 2, 2011 at 4:13 am

    #1402044

    thanks... the error was due to data type mismatch where the SQL is trying to convert an alphanumeric valkues into integer before comparing.

    I have resolved it using ISNUMERIC() function , however, i would still like to know if there is any way i can find a specific row(s) which has such kind of issues in an Update statement and then ignore it from being updated.I'm allowed to write multiple TSQL commands.

Viewing 7 posts - 1 through 6 (of 6 total)

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