Post reply

UPDATE all or only changed values

  • January 21, 2022 at 11:03 am

    #3975577

    UPDATE MyTable

    SET Num = 999

    UPDATE MyTable

    SET Num = 999

    WHERE Num <> 999 --ignoring nulls issue for now

    If a large table in production, I do the second. So treating an UPDATE as changing values only. Though no doubt there are situations where collecting information on overwrites would be important.

    I've assumed the second would performance a little better, but is that true?

     

  • January 21, 2022 at 3:28 pm

    #3975666

    The second will be better by skipping unneccessary data modifications / triggers. No point in updating billions of 999 rows to 999 rows where there are only 3 <> 999 rows

     

    You could change it to

    UPDATE MyTable

    SET Num = 999

    WHERE Num < 999 or Num > 999

    Maybe when there is an index on Num and there is only one Num that is <> 999

     

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

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