Updating multiple rows with SQL and avoiding collisions

Juri shared this tip in his blog post.

I have a table where each row describes an item in an ordered list. Each row has a position value. They are integers, not necessarily contiguous but each unique. A larger position means the row is further down the list. For reordering the rows, I sometimes need to make space between two positions.

BEGIN TRANSACTION;
 
UPDATE table
SET position = -position - 1
WHERE position >= 5;
 
UPDATE table
SET position = -position
WHERE position < 0;
 
COMMIT;

So, to add space for N items before position X:

1. Begin transaction.
2. Instead of incrementing the value on each row greater than or equal to X by N, multiply the value by -1 and decrement it by N.
3. Multiply each value smaller than zero by -1.
4. Commit.