SQL Update Queue Position

Question

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.

Answer 1

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