When you delete one or more rows from a table you'll have gaps in a sequential position column. Here's one solution.
If you delete a row from a table like this...
id position 10 1 11 2 12 3 13 4 14 5 15 6 16 7 17 8
... you'll end up with a gap in the position column...
id position 10 1 11 2 .. . 13 4 14 5 15 6 16 7 17 8
One way would be to update only those rows that need updating, namely 13 to 17. But, what if you delete multiples as below?
id position 10 1 11 2 .. . 13 4 14 5 .. . 16 7 17 8
Well, the same is true. You can first select the position of the lowest deleted row and update where position is greater. Something like this...
UPDATE position SET position = position - 1 WHERE position > THE_POSITION_OF_THE_LOWEST_DELETED_ROW
But, what if two rows next to each other get deleted?
id position 10 1 11 2 12 3 13 4 .. . .. . 16 7 17 8
The previous query won't work as position 7 needs to be updated to 5 but will end up as 6.
So without thinking about it much further, here is what might be a sledgehammer that works.
Delete the row, create a temp table with two columns, id and position. position is an identity column so as we insert into it we will get a new sequence. Then update position using the temp table's for the position column.
DECLARE @id INT SET @id = AN_ID_PASSED_IN DELETE FROM YOUR_TABLE WHERE id = @id CREATE TABLE #YOUR_TEMP_TABLE (id INT, position INT IDENTITY(1, 1)) INSERT INTO #YOUR_TEMP_TABLE ( id ) SELECT id FROM YOUR_TABLE ORDER BY position UPDATE YOUR_TABLE SET position = #YOUR_TEMP_TABLE.position FROM YOUR_TABLE yt INNER JOIN #YOUR_TEMP_TABLE ON yt.id = #YOUR_TEMP_TABLE.id DROP TABLE #YOUR_TEMP_TABLE
To see it in table form, the table and temp table next to each other show how we can join the ids and update the position accordingly.
id pos #id #pos 10 1 10 1 11 2 11 2 12 3 12 3 13 4 13 4 .. . .. . 16 7 16 5 17 8 17 6
Is there a better way? I'd like to think so and would welcome hearing about it.