Ordered updates in SQL



More content will be added to fill this space at some point in the future.

home :: techie :: blog :: programming :: sql :: OrderedUpdates.txt

Ordered updates in SQL

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..