sql - Stored procedure to re-order rows in a table 2 -

i've got 2 tables [pubs] , [authors]. [pubs} can have many [authors] , [authors] can author many [pubs] created junction table [pub_authors] deal many-to-many relationship.

each author given priority on pub and, time time, leading author or publication manager reassign authors new priority on whim.

table structure: pa_id int pub_id int auth_id int pa_priority int old_pri (i added based on possible solution found here) 

the problem: when primary author reassigns priority, need renumber remaining authors' priorities point on , pub.

pa_id   pub_id  auth_id pa_priority  o_pri 1       11      10      1            1 2       11      20      2            2 3       11      30      3            3 

the example above shows data 1 pub 3 authors. publication manager changes priority author 20 2 3.

what should happen:

pa_id   pub_id  auth_id pa_priority  o_pri 1       11      10      1            1 2       11      20      3            2 3       11      30      2            3 

here's sp found in example doesn't quite i'm looking for... changes of priorities "3" instead of reordering them.

alter  proc update_publication_author_priority_order (@id int, @newposition int = 1)  declare @oldposition int,     @direction int  select  @oldposition = pa_priority     pub_authors   pub_id = @id  set     @direction =    case                      when    @oldposition < @newposition 0                     when    @oldposition > @newposition 1                     else    -1                     end  update  t set     oldpri = pa_priority ,     pa_priority =  case                 when    pub_id = @id @newposition                 when    @direction = 0 ,                          pa_priority between @oldposition , @newposition pa_priority - 1                 when    @direction = 1 ,                          pa_priority between @newposition , @oldposition pa_priority + 1                 else    pa_priority                  end    pub_authors t 

i'm there simple fix, i'm overlooking... appreciated.

thanks, bob

ok... after serious consideration, trial , error, have come possible solution. problem multiple instructions during case statement...

update  t set     oldpri = pa_priority,     pa_priority =  case  -- add counter , increment within loop.                 when    pa_priority < @newposition pa_priority                         @counter + 1                 when    pa_priority = @newposition @counter                         @counter + 1                 when    pa_priority > @newposition @counter                         @counter + 1                 else    pa_priority                                                          @counter + 1                 end 

from pub_authors t pub_id in (select publicationid pub_authors pub_id = @id)

the simplest way correct you have fix few places.

select  @oldposition = pa_priority     pub_authors   pub_id = @id 

should

select  @oldposition = pa_priority     pub_authors   pa_id = @id 

then update needs change correct pub_ids , first case needs @ pa_id

update  t set     oldpri = pa_priority,         pa_priority = case when pa_id = @id @newposition                            when @direction = 0                                 , pa_priority between @oldposition , @newposition pa_priority - 1                            when @direction = 1                                 , pa_priority between @newposition , @oldposition pa_priority + 1                            else pa_priority                       end    pub_authors t   pub_id in (select   pub_id                        pub_authors                       pa_id = @id) 

you can using row_number. i've tested few times , seems work.

create procedure update_publication_author_priority_order (      @id int,      @newposition int = 1     )      begin         cte         (             select  *,                     row_number() on (order case when pa_id = @id @newposition else [pa_priority] end, [pa_priority] desc) rn                 pub_authors               pub_id in (select pub_id pub_authors pa_id = @id)         )               update cte set [pa_priority] = rn, [o_pri] = [pa_priority];      end go  

sql fiddle