Sequence Numbering in SQL Server

I have an table (always ordered by ID ascending) with 5 records as such :

ID  Sequence
1   1
2   2
3   3
4   4
8   3
9   3

And the desired output is :

ID  Sequence
1   1
2   2
3   3
4   6
8   4
9   5

Answer

If you want to do this in one step:

update t
    set sequence = v.sequence
    from t join
         (values (4, 6), (8, 4), (9, 5)
         ) v(id, sequence)
         on t.id = v.id;

If you have to do many of these updates, then separate calls to update incur extra overhead.