Update statement to rotate a table 1

[thisisboni]

There is a table with the following test data

PrimaryId	EquipId	Worker	Queue
1	          1	Worker1	  1
3	          1	Worker3	  3
2	          1	Worker2	  4
4	          1	Worker4	  7

Need to write the update the table which will rotate the Workers such that after the update statement the data will look as below

PrimaryId	EquipId	Worker	Queue
1	          1	Worker1	  7
3	          1	Worker3	  1
2	          1	Worker2	  3
4	          1	Worker4	  4

 

[denimined]

Sorry, I don't actually have an environment to test in, but I think this is correct.

The problem is that you cannot actually update any records until you know everything, so you will have to use a temporary table.
Also, from your example data, I am assuming two things:
1. The queue numbering can have gaps.
2. No two entries have the same queue #.

-- Declare stuff.
declare @MinQueue as integer
declare @MaxQueue as integer
declare @PrimaryId as long

-- Need the largest queue
-- as well as the primary of the smallest queue (that will end up becoming the largest).
set @MaxQueue = (select max(queue) from data)
set @MinQueue = (select min(queue) from data)
set @PrimaryId = (select primaryid from data where queue = @MinQueue)

-- Build the replacement set, except for the current smallest.
select dat.primaryid, 
       dt.newqueue as queue
into #tmpdata
from data as dat
join (
      -- Find the largest queue # that is smaller than the current record's queue #.
      select max(queue) as newqueue
      from data
      where primaryid <> dat.primaryid
        and queue     <  dat.queue
     ) as dt
on  dt.primaryid = dat.primaryid
where dat.primaryid <> @PrimaryId

-- Add in the current smallest which will become the largest.
insert into #tmpdata (primaryid, queue)
values (@PrimaryId, @MaxQueue)

-- Now that everything is known, we can replace in one step.
update data
set queue = tda.queue
from data
join #tmpdata as tda
on  tda.primaryid = data.primaryid

-- Housecleaning.  (Comment this out for testing, but then need to manually drop between test cycles.)
Drop #tmpdata