UPDATE SET N + 1 equal to a row

Problem: I need help implementing N+1

WHEN a2 = "Z" then check if the next row number (a1 + 1) has a2 = "Z" ...if this is the case
THEN replace the (a1+1) row --> a2 = "Y" .

MariaDB fiddle

Attempt below: obviously there is a syntax error below, I really don't know how to go about how to find the solution. Any help would be greatly appreciated.

UPDATE a SET CASE 
WHEN a2 = "X" 
AND (a1 + 1) = (a2 = "Z")  
THEN a2 = "Y" END

Input Table: 2 columns (no iteration)

+====+====+
| a1 | a2 |
+====+====+
| 1  | X  |
+----+----+
| 2  | Z  |
+----+----+
| 3  | X  |
+----+----+
| 4  | Y  |
+----+----+
| 5  | Z  |
+----+----+
| 6  | X  |
+----+----+
| 7  | Y  |
+----+----+

Desired Result: 2 columns (no iteration)

+====+====+
| a1 | a2 |
+====+====+
| 1  | X  |
+----+----+
| 2  | Y  |
+----+----+
| 3  | X  |
+----+----+
| 4  | Y  |
+----+----+
| 5  | Z  |
+----+----+
| 6  | X  |
+----+----+
| 7  | Y  |
+----+----+

MariaDB Fiddle:

You have to join the right Sub Select, where your rules apply

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

db<>fiddle here

A Version with gaps would look like this

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

db<>fiddle here

StackExchange Database Administrators Q#294466, answer score: 2