How can I "shift" certain column values to different rows in an existing table?
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