How can I "shift" certain column values to different rows in an existing table? I'm implementing a queue as a ring buffer in a table along the lines of what Thomas Kejser talks about here: Implementing Message Queues in Relational Databases If the ring buffer fills up, I have to increase the number of slots and possibly shift some of the values around. This is part of my routine to add slots to the ring buffer

I have a table that looks like this:

SlotId    SlotVal1  SlotVal2
1          3           3
2          4           4
3          1           1
4          2           2
5          NULL        NULL
6          NULL        NULL
7          NULL        NULL
8          NULL        NULL


I would like to "shift" the values from slots 3 and 4 forward by 4 slots to rows 7 and 8 so the table would look like this:

SlotId  SlotVal1 SlotVal2
1       3        3
2       4        4
3       NULL     NULL
4       NULL     NULL
5       NULL     NULL
6       NULL     NULL
7       1        1
8       2        2


I tried two different UPDATE queries to shift the rows forward and then clear the previous rows.  The first query (as reproduced below) doesn't update any rows in my table and I'm drawing a blank on what I must have left out:

        -- shift slot values forward
        DECLARE @firtRowToMove AS int = 3;  -- slot id of the first row to move
        DECLARE @rowsToShift AS int = 4; -- number of rows forward to shift
        UPDATE [SlotTable] 
        SET [SlotVal1] = (SELECT [SlotVal1]
                          FROM [SlotTable] AS s2
                          WHERE s2.[SlotId] = ([SlotId] - @rowsToShift)),
            [SlotVal2] = (SELECT [SlotVal2]
                          FROM [SlotTable] AS s2
                          WHERE s2.[SlotId] = ([SlotId] - @rowsToShift))
        WHERE [SlotId] &gt;= (@firtRowToMove + @rowsToShift);
You need to qualify SlotId in your sub-query with SlotTable or it will use the SlotId from the sub-query itself.

UPDATE [SlotTable] 
SET [SlotVal1] = (SELECT [SlotVal1]
                  FROM [SlotTable] AS s2
                  WHERE s2.[SlotId] = ([SlotTable].[SlotId] - @rowsToShift)),
    [SlotVal2] = (SELECT [SlotVal2]
                  FROM [SlotTable] AS s2
                  WHERE s2.[SlotId] = ([SlotTable].[SlotId] - @rowsToShift))
WHERE [SlotId] &gt;= (@firtRowToMove + @rowsToShift);


SQL Fiddle

BTW, your CTE version does not work either

Another way to do what you do in this update statement a bit more efficiently (at least in my tests).

update s1
set SlotVal1 = s2.SlotVal1,
    SlotVal2  = s2.SlotVal2
from SlotTable as s1
  inner join SlotTable as s2
    on s2.SlotId = s1.SlotId - @rowsToShift
where s1.SlotId &gt;= @firtRowToMove + @rowsToShift