<p>I have a table which has a field <code>sort_id</code>. In this field there are numbers from 1 to n, that define the order of the data sets.
Now I want to delete some elements and afterwards I want to reorder the table. Therefore I need a query that "finds" the gaps and changes the <code>sort_id</code> field according to the modifications.</p>

<p>Sure, I could do something like this:</p>

<pre><code>SELECT sort_id FROM table WHERE id = 5
</code></pre>

<p>Then save the sort_id and afterwards:</p>

<pre><code>DELETE FROM table WHERE id = 5
UPDATE table SET sort_id = sort_id - 1 WHERE sort_id &gt; {id from above}
</code></pre>

<p>But I'd like to do the reordering process in one step.</p>

for sql server 2005:
this is how you get the new sequence:
```
```
SELECT row_number() over(order by sort_id) as RN
FROM table
```
```
updating the table means you should join that select to your update:
```
```
update t1
set sort_id = t2.RN
FROM table t1 
     join (SELECT row_number() over(order by sort_id) as RN FROM table) t2 
        on t1.UniqueId = t2.UniqueId
```
```