Efficient way to update column with arithmetic sequence of numbers after delete operation

I have a PresentationSlide table:

PresentationSlide
    PresentationSlideId
    PresentationId
    Content
    Order

and example rows:

+---------------------+----------------+---------+-------+
| PresentationSlideId | PresentationId | Content | Order |
+--------+------------+----------------+---------+-------+
|                 123 |              3 | "bla"   |     1 |
|                  23 |              3 | "bla2"  |     2 |
|                  22 |              3 | "bla3"  |     3 |
|                 100 |              3 | "bla4"  |     4 |
|                 150 |              3 | "bla5"  |     5 |
+---------------------+----------------+---------+-------+    

I开发者_开发技巧 want to maintain arithmetic sequence of numbers (1,2,3,4,...) in the Order column after DELETE operation.

For example, if I delete third row (PresentationSlideId = 22), values in order column will be: (1,2,4,5) I want to update Order this way:

PresentationSlideId = 100:  update order from 4 to 3
PresentationSlideId = 150:  update order from 5 to 4

How is the most efficient way to do this kind of update? Is any way to do this with using only one UPDATE statement? I could do this using cursor and loop, but it doesn't seems efficient.

---

1) Order is a very poor name for a column, since it's an SQL Keyword

2) It would be a lot better if you could cope with gaps in the order (and possibly switch to using a float, so you can insert fractional values), because in your current model, every insert, update or delete is potentially going to affect the entire table. This doesn't scale well. Computing an order using ROW_NUMBER() during selects would generally be better.

3)

create table #PresentationSlide (
    PresentationSlideID int not null,
    PresentationId int not null,
    Content varchar(10) not null,
    [Order] int not null
)
insert into #PresentationSlide (PresentationSlideId , PresentationId , Content , [Order])
select 123,3,'bla',1 union all
select 23,3,'bla2',2 union all
select 22,3,'bla3',3 union all
select 100,3,'bla4',4 union all
select 150,3,'bla5',5


delete from #PresentationSlide where PresentationSlideId = 22

;With Reorder as (select PresentationSlideId,ROW_NUMBER() OVER (ORDER BY [Order]) as NewOrder from #PresentationSlide)
update ps set [Order] = NewOrder
from #PresentationSlide ps inner join Reorder r on ps.PresentationSlideId = r.PresentationSlideId

select * from #PresentationSlide order by [Order]

drop table #PresentationSlide

---

;with C as
(
  select [Order],    
         row_number() over(order by [Order]) as rn
  from PresentationSlide 
)
update C set
  [Order] = rn