Update the column which has sequence number dynamically

Question

I have a table which has two column rID, SequenceNo values will be something like

Table name - Maintable

rid	seqno
r1	1
r2	2
r3	3
r4	4
r5	5

Please note above is just an example there can N no of rows.

Now if we get a request like

r1 needs to have sequence no 5
r2 needs to have sequence no 4
r5 needs to have sequence no 2

So the request for update will be got as below,

**create table #apprevisedsequence
(
mid varchar(10),
appsequence int
)

insert into #apprevisedsequence
select r1,5
insert into #apprevisedsequence
select r2,4
insert into #apprevisedsequence
select r5,2


update a set a.seqno = b.Appsequence
from maintable a join
#apprevisedsequence b on a.rid = b.mid**

The above code will handle the required seqno for r1, r2 and r5. But we need to update r3 and r4 in a sequential way such that r3 is updated as 1 and r4 updated as 3 since that has the highest sequential order previously. Can anyone help me with script so that this can handle with even 10 rows or 20 rows and request can be 5 or 10 rows.

So my expected result set will be like

rid	seqno
r3	1
r5	2
r4	3
r2	4
r1	5

We can update the request as received but not sure how we can update the remaining rows based on range

Answer 1

This might be a bit long winded but it works.

The idea is to get a list of fixed values (the 3 you know), then a list of RIDs with no fixed value and a list of seq numbers with no fixed value, assign each a row number an then join these to fill in the gaps.

DECLARE @t TABLE (rid varchar(10), seqno int);
INSERT INTO @t VALUES 
('r1', 1),
('r2', 2),
('r3', 3),
('r4', 4),
('r5', 5);

DECLARE @fixed TABLE(rid varchar(10), seqno int);
INSERT INTO @fixed VALUES 
('r5', 2),
('r2', 4),
('r1', 5);

WITH SeqenceNos AS 
    (SELECT t.seqno, ROW_NUMBER() OVER(ORDER BY t.seqno) as RowN 
        FROM @t t 
        LEFT JOIN @fixed f on t.seqno = f.seqno 
        WHERE f.seqno is null)
, 
RIDs AS 
(SELECT t.rid, ROW_NUMBER() OVER(ORDER BY t.rid) as RowN 
    FROM @t t 
    LEFT JOIN @fixed f on t.rid = f.rid 
    WHERE f.rid is null)

UPDATE t 
    SET seqno = ISNULL(f.seqno, s.seqno)
    FROM @t t 
    LEFT JOIN @fixed f on t.rid = f.rid
    LEFT JOIN RIDs r on t.rid = r.rid
    LEFT JOIN SeqenceNos s on r.RowN = s.RowN;

SELECT * FROM @t;

Here's a fiddle of it in action

https://dbfiddle.uk/xMnR-QCE