Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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] >= (@firtRowToMove + @rowsToShift);
share|improve this question
3  
Are you trying to make this a generic "thing" or is this a one time type of activity? If it's generic, could you help my poor brain understand the logic for how one would determine how far forward a thing advances? –  billinkc Jul 26 '13 at 19:27
2  
Are these INT columns? Can you describe the difference between -- and NULL? –  Aaron Bertrand Jul 26 '13 at 19:28
    
@AaronBertrand, those are int columns and I used the "--" only to indicate where the values came from. They would be updated to NULL as part of the same transaction. –  Dan Jul 26 '13 at 19:30
    
@billinkc I don't think the logic for determining the number of rows to shift matters, but if you want to know the larger context, I'm implementing a queue as a ring buffer in a table along the lines of what Thomas Kejser talks about here: blog.kejser.org/2012/05/25/…. 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. –  Dan Jul 26 '13 at 19:34
    
Will the destination rows always exist or does a solution need to determine insert/update logic as well? –  billinkc Jul 26 '13 at 19:34

2 Answers 2

up vote 4 down vote accepted

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] >= (@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 >= @firtRowToMove + @rowsToShift
share|improve this answer
    
Ah, that makes sense now. As an aside, my actual CTE query was working, but it was because I had aliased the SlotId column in the subquery so it was no longer ambiguous. I just failed to include that detail in the simplified version of the query that I posted here. And thanks for the more efficient version of the query. –  Dan Jul 27 '13 at 12:49

I got a version of the query with a CTE to work for me, but as Mikael Eriksson points out in his answer, it had nothing to do with using a CTE and everything to do with fully qualifying the ambiguous reference to the SlotId column in the sub-select.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.