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'),
(2, 'Z'),
(3, 'X'),
(4, 'Y'),
(5, 'Z'),
(6, 'X'),
(7, 'Y');





select * from a order by a1;


a1
a2


1
X

2
Z

3
X

4
Y

5
Z

6
X

7
Y


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


col1
col2


2
Y

3
null

4
null

5
null

6
null

7
null

8
null


UPDATE a 
INNER JOIN (SELECT  a1+1 as col1, CASE 
WHEN a2 = "X" 
AND (SELECT a2 FROM a WHERE t1.a1+1 = a1)   = "Z"
THEN "Y" ELSE NULL END as col2 from a t1 order by a1) 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

2
Y

3
X

4
Y

5
Z

6
X

7
Y

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