SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table RentalQueueTest
(
    MemberId INT
    , DVDID INT
    , RentalQueuePosition INT
)
Insert into RentalQueueTest values (1, 2, 1);
Insert into RentalQueueTest values (1, 3, 2);
Insert into RentalQueueTest values (1, 4, 3);
Insert into RentalQueueTest values (1, 5, 4);
4 rows affected

DECLARE @memberId INT = 1;
DECLARE @dvdId INT = 6;
DECLARE @position INT = 2;

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

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

SELECT * FROM RentalQueueTest ORDER BY RentalQueuePosition


MemberId
DVDID
RentalQueuePosition


1
2
1

1
6
2

1
3
3

1
4
4

1
5
5


DELETE FROM RentalQueueTest WHERE DVDId = 1
DECLARE @memberId INT = 1;

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 
5 rows affected

SELECT * FROM RentalQueueTest ORDER BY RentalQueuePosition


MemberId
DVDID
RentalQueuePosition


1
2
1

1
6
2

1
3
3

1
4
4

1
5
5


DECLARE @memberId INT = 1;
DECLARE @dvdId INT = 7;
DECLARE @position INT = 4;

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

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

SELECT * FROM RentalQueueTest ORDER BY RentalQueuePosition


MemberId
DVDID
RentalQueuePosition


1
2
1

1
6
2

1
3
3

1
7
4

1
4
5

1
5
6