home :: techie :: blog :: programming :: sql :: OrderedUpdates.txt
Why doesn't the update command in SQL support an "order by" clause?
Logically you'd think "That'll be because the order doesn't matter" - SQL is all mathematical set operations. Unfortunately it does.
Think of the case (that you shouldn't do), of altering the primary key of a table:
update my_table set id = id + 1;
Think of this as being like Hilbert's Hotel - if there were no collisions before running it (and nothing added to it whilst the update runs) then there should be no collisions afterwards.
Unfortunately you do get an error indicating a violation of the primary key uniqueness constraint. Obviously it sets the id of the row which used to have id=1 to be id=2 then checks for the constraint at this point there is already a row with id=2 (that we're intending to make id=3). If we updated them in order from the highest id first, then there wouldn't be any problem as we would free up a "room" before trying to put anyone else in it.
Last updated: 12:06, 25 Jun 2008 [ /techie/blog/programming/sql ] Link..