## All we need is an easy explanation of the problem, so here it is.

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:

https://sqlize.online/?phpses=&sqlses=0ba70679544970b2695b25384f7f76bb&php_version=null&sql_version=mariadb

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 |
+----+----+
```

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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 |null4 |null5 |null6 |null7 |null8 |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 |null5 |null6 |null7 |null8 |nullnull|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*

### Method 2

Another approach is to use the window function `LAG`

. Something like:

```
update a
set a2 = 'Y'
where exists (
select 1 from (
select a1, a2, lag(a2) over (order by a1) as lag_a2
from a
) as t
where t.a2 = 'Z'
and t.lag_a2 = 'X'
and t.a1 = a.a1
);
```

If the current a2 is ‘Z’ and the previous one is ‘X’, the row should be updated

**Note: Use and implement method 1 because this method fully tested our system.Thank you 🙂**

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0