MariaDB 10.5

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

CREATE TABLE `a` (
  `a1` int(20) NOT NULL,
  `a2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--

INSERT INTO `a` (`a1`,  `a2`) VALUES
(1,  'X'),
(3, 'Z'),
(4, 'X'),
(5, 'Y'),
(6, 'Z'),
(7, 'X'),
(8, 'Y');





select * from a order by a1;


a1
a2


1
X

3
Z

4
X

5
Y

6
Z

7
X

8
Y


WITH cte As (select *, ROW_NUMBER() OVER() as rn from a order by a1)
SELECT (SELECT a1 FROM cte WHERE rn = t1.rn+1) as col1, CASE 
WHEN a2 = "X" 
AND (SELECT a2 FROM cte WHERE t1.rn+1 = rn)   = "Z"
THEN "Y" ELSE NULL END as col2 from cte t1 order by rn;


col1
col2


3
Y

4
null

5
null

6
null

7
null

8
null

null
null


UPDATE a 
INNER JOIN (WITH cte As (select *, ROW_NUMBER() OVER() as rn from a order by a1)
SELECT (SELECT a1 FROM cte WHERE rn = t1.rn+1) as col1, CASE 
WHEN a2 = "X" 
AND (SELECT a2 FROM cte WHERE t1.rn+1 = rn)   = "Z"
THEN "Y" ELSE NULL END as col2 from cte t1 order by rn) t2 ON a.a1 = t2.col1 AND t2.col2 IS NOT NULL
SET a2 = col2
select * from a order by a1;


a1
a2


1
X

3
Y

4
X

5
Y

6
Z

7
X

8
Y

col1	col2
3	Y
4	null
5	null
6	null
7	null
8	null
null	null