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

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

MariaDB Fiddle:

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 🙂

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

Leave a Reply