SQL Update Queue Position-CodePudding

I have a simple DVD rental queue where I would like to update the position numbers based on the customer's preference.

So I have the following schema of the Rental Queue:

Create Table RentalQueueTest
(
    MemberId INT
    , DVDID INT
    , RentalQueuePosition INT
)

I created the following sequence:

Create sequence rentalQueuePosition start with 1

Here is the data:

Insert into RentalQueueTest values (1, 2, next value for rentalQueuePosition)
Insert into RentalQueueTest values (1, 3, next value for rentalQueuePosition)
Insert into RentalQueueTest values (1, 4, next value for rentalQueuePosition)
Insert into RentalQueueTest values (1, 5, next value for rentalQueuePosition)

Now when I make another insert into the RentalQueueTest table like this:

Insert into RentalQueueTest values (1, 6, 2)

From the previous insert statement, we can see that customer 1 wants DVD 6 to be in 2nd place. Hence, I would like to update the queue position of the previous inserts (ie. (1,3), (1,4) etc...What would be the best way to achieve this? Thanks in advance.

CodePudding user response：

If you do not need to validate that 2 is indeed the second item in the list, so we do not need to take deletions or removals into account then you can simply update the list first so that all records with a value equal to or greater than the position of the new insert are incremented by 1:

UPDATE RentalQueueTest SET RentalQueuePosition = RentalQueuePosition   1
WHERE MemberId = @memberId 
  AND RentalQueuePosition >= @position;

INSERT INTO RentalQueueTest VALUES (@memberId, @dvdId, @position);

If you need the positions to be contiguous then it might be necessary to first re-order the items before the above query.

If contiguous is important and deletions are possible, then you could simply use the reverse of the above logic, to reduce the positions by 1 as part of the DELETE process. But it is generally safer to write a script that re-numbers the whole set rather than relying on record-by-record processing.

UPDATE RentalQueueTest SET RentalQueuePosition = Position
FROM RentalQueueTest list
INNER JOIN (
  SELECT MemberId
       , DVDID
       , ROW_NUMBER() 
         OVER (PARTITION BY MemberId 
               ORDER BY RentalQueuePosition) as Position
         FROM RentalQueueTest
         WHERE MemberId = @memberId 
) as numberedList ON list.MemberId = numberedList.MemberId AND list.DVDId = numberedList.DVDId
WHERE list.MemberId = @memberId

Here is a fiddle for an extended proof: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a4e96f6e4a3c63380492ae985a4df236

CodePudding user response：

This is the simplest way if the above use case is a one time necessity and the order doesn't matter:

update RentalQueueTest
set RentalQueuePosition = RentalQueuePosition   1 
where RentalQueuePosition >= 2 and DVDID <> 6