SQL

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.