Update other row(s) with sequencing value based on previously updated row

I have a table like this:

SELECT ID, Rank
FROM FAQ
ORDER BY Rank ASC;

Now, I'm updating a row:

UPDATE FAQ SET
    Rank = 8
WHERE ID = 30;

SELECT ID, Rank
FROM FAQ
ORDER BY Rank ASC;

My question: is there a way to update the other row ID (23, 24, 26, 27, 28) from original Rank (4, 5, 6, 7, 8) to new Rank (3, 4, 5, 6, 7), respectively?

I have tried:

DECLARE @SrNo SMALLINT = 0;

UPDATE FAQ SET @SrNo = Rank = @SrNo + 1;

after the update but to no avail, as it will just return the Rank value as per the first image.

Thanks in advance.

You can use the code below and create a stored procedure:

DECLARE  @FAQ TABLE (
    ID INT,
    [Rank] INT
);

INSERT INTO @FAQ (ID, [Rank])
VALUES
    (21, 1),
    (25, 2),
    (30, 3),
    (23, 4),
    (24, 5),
    (26, 6),
    (27, 7),
    (28, 8),
    (29, 9),
    (22, 10);

DECLARE @ID INT = 30
	   ,@Rank INT = 8;

WITH DataSource AS
(
	SELECT *
		  ,ROW_NUMBER() OVER (ORDER BY IIF(ID = @ID, @Rank, [Rank]), IIF(ID = @ID, IIF(@Rank > [Rank], 1, -1), 0)) AS [new_rank] AS [new_rank]
	FROM @FAQ
)
UPDATE DataSource
SET [Rank] = [new_rank]
WHERE [Rank] <> [new_rank];

SELECT ID, [Rank]
FROM @FAQ
ORDER BY [Rank] ASC;

We are using ROW_NUMBER in order to order the data based on the new rank value for a particular row. For rows with the same value, the updated one is treated as bigger.

A slightly different, and possibly more efficient, version of @gotqn's excellent answer:

DECLARE  @FAQ TABLE (
    ID INT primary key,
    [Rank] INT
);

INSERT INTO @FAQ (ID, [Rank])
VALUES
    (21, 1),
    (25, 2),
    (30, 3),
    (23, 4),
    (24, 5),
    (26, 6),
    (27, 7),
    (28, 8),
    (29, 9),
    (22, 10);

DECLARE @ID INT = 30
       ,@Rank INT = 8;
DECLARE @OldRank INT = (SELECT Rank FROM @FAQ RowToMove WHERE RowToMove.ID = @ID);

UPDATE ToUpdate
SET Rank = IIF(ToUpdate.ID = @ID, @Rank, ToUpdate.Rank + SIGN(@OldRank - @Rank))
FROM @FAQ ToUpdate
WHERE ToUpdate.Rank BETWEEN
    LEAST(@OldRank, @Rank) AND GREATEST(@OldRank, @Rank);

SELECT ID, [Rank]
FROM @FAQ
ORDER BY [Rank] ASC;

Essentially, you begin by looking up the existing rank of the single row you want to move.

Then take all the rows between that rank and the new rank (taking the lower and higher of those in order). Update that set of rows as follows: if it's the row we want to change, set it to @Rank, otherwise add to it the SIGN of the difference between the rank moves ie +1 or -1. This therefore works no matter which direction the row is moving.

It also does not require a sort on the rows, as long as there is an index on ID

db<>fiddle

Generate Row_Number order by Id and hopefully it will satisfy your desired output. Make sure how you generating Rank order by. Because 22 with rank 10

with cte as (
    select *, row_number() over (order by Id) as RwNo from FAQ 
)
update cte set Rank = RwNo; 

Generation of the Rank on the fly is best approach instead of storing in the table.