How to control order of Update query execution?

Question

I have a table in MS SQL 2005. And would like to do:

update Table
set ID = ID + 1
where ID > 5

And the problem is that ID is primary key and when I do this I have an error, because when this query comes to row with ID 8 it tries to change the value to 9, but there is old row in this table with value 9 and there is constraint violation.

Therefore I would like to control the update query to make sure that it's executed in the descending order.

So no for ID = 1,2,3,4 and so on, but rather ID = 98574 (or else) and then 98573, 98572 and so on. In this situation there will be no constraint violation.

So how to control order of update execution? Is there a simple way to acomplish this programmatically?

Answer 1

Try this:

update Table
set ID = ID * 100000 + 1
where ID > 5

update Table
set ID = ID / 100000
where ID > 500000

Answer 2

Don't forget the parenthesis...

update Table set ID = (ID * 100000) + 1 where ID > 5

If the IDs get too big here, you can always use a loop.

Answer 3

Transact SQL defers constraint checking until the statement finishes.

That's why this query:

UPDATE  mytable
SET     id = CASE WHEN id = 7 THEN 8 ELSE 7 END
WHERE   id IN (7, 8)

will not fail, though it swaps id's 7 and 8.

It seems that some duplicate values are left after your query finishes.

Answer 4

Personally I would not update an id field this way, I would create a work table that is the old to new table. It stores both ids and then all the updates are done from that. If you are not using cascade delete (which could incidentally lock your tables for a long time), then start with the child tables and work up, other wise start with the pk table. Do not do this unless you are in single user mode or you can get some nasty data integrity problems if other users are changin things while the tables are not consistent with each other.

PKs are nothing to fool around with changing and if at all possible should not be changed.

Before you do any changes to production data in this way, make sure to take a full backup. Messing this up can cost you your job if you can't recover.