省略了一个方言，我用t-sql回答了这个问题，因为我想尝试一下。
这并不漂亮，但是，我使用几个可更新的cte来查找特定行的偏移量，然后相应地更新所需的行：

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (AdmissionNo int, Rollno tinyint, [Name] char(1));

INSERT INTO dbo.YourTable
VALUES(1001,1,'A'),
      (1003,2,'B'),
      (1005,3,'C'),
      (1006,4,'D'),
      (1008,5,'E');
GO

DECLARE @NewPosition tinyint = 2,
        @MovingName char(1) = 'D';

WITH Offsetting AS(
    SELECT *,
           COUNT(CASE Rollno WHEN @NewPosition THEN 1 END) OVER (ORDER BY RollNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) -
           COUNT(CASE [Name] WHEN @MovingName THEN 1 END) OVER (ORDER BY RollNo ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS LagOffset       
    FROM dbo.YourTable),
NewNames AS(
    SELECT *,
           CASE RollNo WHEN @NewPosition THEN @MovingName
                                         ELSE LAG([Name],LagOffset) OVER (ORDER BY RollNo)
           END AS NewName
    FROM Offsetting)
UPDATE NewNames
SET [Name] = NewName;
GO

SELECT *
FROM dbo.YourTable;

GO

DROP TABLE dbo.YourTable;

不太好，但你可以使用一些子查询

DROP TABLE IF EXISTS T;
create table t
(AdmissionNo int,  RollNo int,  Name varchar(1)); 
insert into t values
(1001       ,       1   ,  'A'),  
(1003       ,       2   ,  'B'), 
(1005       ,       3   ,  'C'),  
(1006       ,       4   ,  'D'),  
(1008       ,       5   ,  'E');

select t.*,
         case when rollno = 2 then (select name from t where rollno = 4)
         when rollno > 2 and 
               rollno <> (select max(rollno) from t) then (select name from t t1 where t1.rollno < t.rollno order by t1.rollno desc limit 1)
         else name
         end    
from t;

+-------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1001 |      1 | A    | A                                                                                                                                                                                                                                       |
|        1003 |      2 | B    | D                                                                                                                                                                                                                                       |
|        1005 |      3 | C    | B                                                                                                                                                                                                                                       |
|        1006 |      4 | D    | C                                                                                                                                                                                                                                       |
|        1008 |      5 | E    | E                                                                                                                                                                                                                                       |
+-------------+--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.001 sec)

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(admission_no INT NOT NULL PRIMARY KEY
,roll_no INT NOT NULL
,name CHAR(1) NOT NULL
);

INSERT INTO my_table VALUES
(1001,1,'A'),
(1003,2,'B'),
(1005,3,'C'),
(1006,4,'D'),
(1008,5,'E');

SELECT *
     , CASE WHEN roll_no = 4 THEN 2 
            WHEN roll_no >= 2 AND roll_no < 4 THEN roll_no + 1 
            ELSE roll_no END x FROM my_table;
+--------------+---------+------+---+
| admission_no | roll_no | name | x |
+--------------+---------+------+---+
|         1001 |       1 | A    | 1 |
|         1003 |       2 | B    | 3 |
|         1005 |       3 | C    | 4 |
|         1006 |       4 | D    | 2 |
|         1008 |       5 | E    | 5 |
+--------------+---------+------+---+
5 rows in set (0.00 sec)

…或者，作为更新。。。

UPDATE my_table x
  JOIN 
     ( SELECT *
            , CASE WHEN roll_no = 4 THEN 2 
                   WHEN roll_no >= 2 AND roll_no < 4 THEN roll_no + 1 
                   ELSE roll_no END n  
         FROM my_table
     ) y
    ON y.admission_no = x.admission_no
   SET x.admission_no = y.n;

您可能想扩展这个想法来处理行可以在列表中上下拖动的事实，所以类似这样的东西。。。

SET @source = 1, @target = 5;

SELECT *
     , CASE WHEN roll_no = GREATEST(@source,@target) THEN LEAST(@source,@target)
            WHEN roll_no >= LEAST(@source,@target) AND roll_no < GREATEST(@source,@target) THEN roll_no + 1 
            ELSE roll_no END x 
  FROM my_table;

尝试下面的查询

; with cte as (select a.AdmissionNo, a.RollNo, b.Name from student a
join student b on a.RollNo=b.RollNo+1
where a.RollNo between 3 and 4

union all

select a.AdmissionNo, a.RollNo, b.Name from student a
left join student b on a.RollNo+2=b.RollNo
where a.RollNo=2)
update a set a.Name = b.name
from student a
join cte b on a.rollno=b.rollno