Updating multiple rows with SQL and avoiding collisions
I ran into an interesting problem with SQL the other day: how do you update multiple rows while maintaining an uniqueness constraint?
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.
How do you do that? Well, the obvious answer is a simple update statement, assuming you want to make space for one item before position 5:
UPDATE table
SET position = position + 1
WHERE position >= 5
Despite having used SQL since the 90s I don't think I've ever needed to do this before. It seemed simple enough, but I found out the solution I went with is not only obvious but also wrong. When you have contiguous position values, that statement causes a unique constraint violation, in both SQLite and PostgreSQL. Having transaction isolation doesn't prevent collisions during updates, even if the end state would be valid.
A helpful LLM tried to suggest the broken solution, then a solution that caused a syntax error and then a solution that involved creating a temporary table. After that I went back to searching the web and finally found a mention about negating the values temporarily which sounded like way less hassle than temporary tables.
That worked great. So, to add space for N items before position X:
- Begin transaction.
- 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.
- Multiply each value smaller than zero by -1.
- Commit.
BEGIN TRANSACTION;
UPDATE table
SET position = -position - 1
WHERE position >= 5;
UPDATE table
SET position = -position
WHERE position < 0;
COMMIT;