# UPDATE SET N + 1 equal to a row

## 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" .

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

### 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 🙂