Strange behavior of UPDATE statement

  • I have noticed more than several times that when I try to run an UPDATE statement it will show in the results pane "(1) row affected" for each record that gets updated, as opposed to just saying "(15) rows affected" (assuming only 15 records were touched). Has anyone every experienced this before?

  • you get a rows affected for every sql statement performed. that means select,update,insert or delete statements each return the rows affected.

    sounds like you had either a cursor or loop that was affecting one row at a time, instead of a single update statement that affected mulitple rows, or maybe some select statements and then an update.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It may be the way your update is written. Could you post the code you are running for us to look at?

    😎

  • Thanks for taking the time to reply.

    In this example, 63 records should be updated, however the results show that each record is updated (and reported on separately). The very last line of the results says (63 row(s) affected):

    Example SQL:

    UPDATE LUS_CURR_PERD_RATE

    SET ACNT_FROM = 'x'

    WHERE PERD = 0

    Results:

    ----------------------------------------------------------

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    ......... 53 more lines of (1 row(s) affected) .........

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    (63 row(s) affected)

  • You have a trigger(s) that update rows one at the time.

  • How would I confirm that it is actually a trigger that is causing this to happen? I am assuming I could try running sp_helptrigger LUS_CURR_PERD_RATE as a starting point, right?

  • One thing that is odd though, is that there is another table which has the exact same structure and atrributes as the table I used in this example (with different data) that does not show "(1) row affected" for each record that gets updated.

  • Yup 🙂 That'll give you the names of any triggers on the table. Once you have the names, you can use sp_helptext to get the text of the trigger.

    If you can, post it here. There are some people around here who get a bit of a kick out of converting cursor-based code to set-based code.

    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
  • Yup... there are already at least three of us waiting for the code to cure it :hehe:.

  • Nick Via (10/3/2007)


    One thing that is odd though, is that there is another table which has the exact same structure and atrributes as the table I used in this example (with different data) that does not show "(1) row affected" for each record that gets updated.

    Nothing odd there at all. Triggers exist on individual tables. So the table that does show the odd results has a trigger on it and the one that doesn't, doesn't have a trigger.

    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
  • I found that there is a trigger on the table, but unfortunately when I ran sp_helptext on it I got the following message: "The object comments have been encrypted."

    So it looks like the trigger definition is encrypted. Which I guess makes sense since the database itself is apart of a commercial software package.

    I should also note that the other table that doesn't have this problem has the same trigger, at least in name, on it as well.

    I'm not really sure what, if anything, I can do at this point.

  • There are decrypting scripts on this site and on google.

    Take a backup of the db and restore it on another server. Then run those scripts on tha RESTORED version.

    Get the code, post it here. We'll correct the code. Then you can place a complaint on the vendor and I'll send you a link supporting the fact that this is a performance hazard for you server and that they must fix their code.

  • Thanks Ninja's_RGR'us!

    I will try to find one of those decryption scripts so I can post the trigger's definition.

Viewing 14 posts - 1 through 13 (of 13 total)

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