UPDATE SET N + 1 等於一行

問題：我需要幫助實現 N+1

WHEN a2 = “Z” 然後檢查下一個行號 (a1 + 1) 是否有 a2 = “Z” …如果是這種情況則
替換 (a1+1) 行–> a2 = “Y” 。

MariaDB 小提琴： https ://sqlize.online/?phpses=&sqlses=0ba70679544970b2695b25384f7f76bb&php_version=null&sql_version=mariadb

嘗試下面：顯然下面有語法錯誤，我真的不知道如何去尋找解決方案。任何幫助將不勝感激。

---

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

---

輸入表：2列（無迭代）

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

期望的結果：2列（無迭代）

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

MariaDB 小提琴：

您必須加入正確的子選擇，您的規則適用

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 | 從
 3 | X
 4 | 是
 5 | 從
 6 | X
 7 | 是

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 | 是 
 3 | *空*
 4 | *空*
 5 | *空*
 6 | *空*
 7 | *空*
 8 | *零*

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 | 是
 3 | X
 4 | 是
 5 | 從
 6 | X
 7 | 是

db<>在這裡擺弄

有間隙的版本看起來像這樣

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 | 從
 4 | X
 5 | 是
 6 | 從
 7 | X
 8 | 是

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 | 是 
 4 | *空*
 5 | *空*
 6 | *空*
 7 | *空*
 8 | *空空*
| *_* *空值*

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 | 是
 4 | X
 5 | 是
 6 | 從
 7 | X
 8 | 是

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/294466