SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table tmp (Id int, name varchar(4), mark int);

insert into tmp values
(1   ,'Medi',    10),
(2   ,'Ibra',    15),
(3   ,'Simo',    20),
(4   ,'Alfa',    25),
(5   ,'Beta',    30),
(6   ,'Cher',    35),
(7   ,'Dean',    40) ;
7 rows affected

select * from tmp order by id ;


Id
name
mark


1
Medi
10

2
Ibra
15

3
Simo
20

4
Alfa
25

5
Beta
30

6
Cher
35

7
Dean
40


WITH cte AS (
  SELECT *,
    1 + COUNT(1) OVER () 
          - 2 * ROW_NUMBER () OVER (ORDER BY ID)
    AS rn
  FROM tmp
),
  
ctex AS (
  SELECT *, ABS(rn) AS arn
  FROM cte
),
  
cte2 AS (
  SELECT *,
    CASE WHEN rn > 0
      THEN LEAD(name, arn) OVER (ORDER BY id)
      ELSE  LAG(name, arn) OVER  (ORDER BY id)
    END AS name_r,
    CASE WHEN rn > 0
      THEN LEAD(mark, arn) OVER (ORDER BY id)
      ELSE LAG(mark, arn) OVER  (ORDER BY id)
    END AS  mark_r
  FROM ctex
)
UPDATE cte2
SET name = name_r,
    mark = mark_r
 ;
7 rows affected

select * from tmp order by id ;


Id
name
mark


1
Dean
40

2
Cher
35

3
Beta
30

4
Alfa
25

5
Simo
20

6
Ibra
15

7
Medi
10