Post reply

Update Statement appears to work, but only updates 1 row

  • October 18, 2011 at 6:26 am

    #249665

    I'm trying to update 6,000 rows in a single table using this statement (Person is the table name), and I'm trying to blank out the contents of the field UniversityName(nchar(75)) for all students with a Program year (nchar(4)) of 2011.:

    UPDATE Person

    Set Pers_UniversityName = NULL

    Where Pers_ProgramYear = '2011'

    It says 6,000 rows were affected, but when I look at the data, it really only updated 1 row. I am logged in as sa, so I don't believe it's a rights issue.

    I'm stumped. Please help:)

  • October 18, 2011 at 6:31 am

    #1395859

    Missing a commit?

    Missed a rollback (maybe a trigger)?

    Are you sure the select / update match in the where?

  • October 18, 2011 at 6:45 am

    #1395872

    Often I'll test the criteria with a select statement before running an update statement with the same criteria. Obviously done outside of a transaction this isn't foolproof as other updates could happen in the meantime but I do it anyways.

    Could it be reporting 6000 updates for records where the Pers_UniversityName was already NULL ? Maybe you could try this and see if you get different results:

    UPDATE Person

    Set Pers_UniversityName = NULL

    Where Pers_ProgramYear = '2011'

    AND Pers_UniversityName IS NOT NULL

  • October 18, 2011 at 6:57 am

    #1395884

    I did run the select first to make sure I had the field names correct, and it returned a specific number of records, then I ran the update, and it said the total number of records were affected. When I run the select statement after that, it shows one less record. So the statement is working, but it's only updating 1 row.

  • October 18, 2011 at 6:58 am

    #1395886

    I like the trigger theory best.

    SELECT [name]

    FROM sys.triggers

    WHERE OBJECT_NAME(parent_id) = 'Person'

    John

  • October 18, 2011 at 7:01 am

    #1395888

    Instead of trigger not made to handle multi rows is the most likely culprit.

    Unless we're missing clues or the current assumptions are wrong.

  • October 18, 2011 at 7:15 am

    #1395899

    Aha! It was a trigger - there was a trigger on that table that was set up to track changes to the table.

    I scripted out the trigger, deleted the trigger, then ran my update, then put the trigger back.

    Thanks for the assistance.

  • October 18, 2011 at 7:18 am

    #1395901

    ksnyder-1129141 (10/18/2011)

    Aha! It was a trigger - there was a trigger on that table that was set up to track changes to the table.

    I scripted out the trigger, deleted the trigger, then ran my update, then put the trigger back.

    Thanks for the assistance.

    It's better to disable it for the ms you run the update. That way other statements can still use the trigger.

  • October 18, 2011 at 7:27 am

    #1395913

    I tried to disable it, and it wouldn't let me.

  • October 18, 2011 at 7:29 am

    #1395916

    ksnyder-1129141 (10/18/2011)

    I tried to disable it, and it wouldn't let me.

    What was the error??

  • October 18, 2011 at 7:37 am

    #1395926

    "Could not load type 'Microsoft.SqlServer.Management.SqlManagerUI.DisableTrigger' from Assembly 'Microsoft.SqlServer.Express.SqlManagerUI, Version=9.0.242.0, Culture=neutral, PublicKeyToken =......."

  • October 18, 2011 at 7:41 am

    #1395933

    I'd make sure to debug that.

    You might have a corrupt installation.

  • October 18, 2011 at 7:42 am

    #1395934

    Will do. Thanks for the help.

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

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