從遞歸 CTE (MySQL/MariaDB) 更新列

我需要更新用於創建遞歸 cte 的同一個表。cte 用作選擇語句，但我無法將這些值實際插入表中。

我的程式碼如下 - 基本上我想：

UPDATE tbl_1min
SET tbl_1min.ema9 = x.NewEma
WHERE x.id = tbl_1min.id

任何人都可以幫忙嗎？我真的很感激！

SET @NDays := 9;

WITH RECURSIVE anchor AS
   (
       SELECT
           a1.id,
           a1.close,
           a1.ema9
       FROM
           tbl_1min a1
),
x AS
   (
       SELECT
           anchor.id,
           anchor.close,
           x2.ema9 AS PrevEma,
           anchor.ema9,
           anchor.ema9 AS NewEma
       FROM
           anchor
       INNER JOIN
           anchor x2
       ON
           anchor.id - 1 = x2.id
       WHERE anchor.id = @NDays -- FIRST SEED ROW
UNION
  SELECT
   anchor.id,
   anchor.close,
   x.PrevEma,
   anchor.ema9,
   (((2 / (1 + @NDays)) * (anchor.close - x.PrevEma)) + x.PrevEma) AS NewEma
   FROM
       x
   JOIN anchor
   ON anchor.id = x.id + 1   -- `X` WALKS ITSELF
   )
SELECT
   id,
   NewEma
FROM 
   x
ORDER BY x.id
;

為此，我將遞歸 CTE 更改為將錨點和子項都放在與標準相同的 CTE 中。這樣，我就能夠展示原始表格是如何更新的。

答案由與查詢相關的 3 個部分組成：

1. 顯示表原始內容的查詢
2. 基於遞歸 CTE 的更新
3. 顯示更新的原始表的查詢 - 在本例中為 2 行。

根據您的其他問題更新

CREATE TABLE tbl_1min(id int, close1 int,ema9 double);
INSERT INTO tbl_1min VALUES 
(1,1,10),
(2,1,15),
(3,1,25),
(4,1,30),
(5,1,35),
(6,1,40),
(7,1,45),
(8,1,50),
(9,1,55),
(10,1,60),
(11,1,65);

select * from tbl_1min;

SET @NDays := 9;
WITH RECURSIVE cte AS
(
 SELECT a.id, a.close1, a.ema9 AS PrevEma, a.ema9, a.ema9 AS NewEma
 FROM tbl_1min a
 WHERE a.id = @NDays -- FIRST SEED ROW
 UNION ALL
 SELECT y.id, y.close1, x.NewEma AS PrevEma, y.ema9,
 ((2 / (1 + @NDays) * (y.close1 - x.NewEma)) + x.NewEma) AS NewEma
 FROM cte x JOIN tbl_1min y ON y.id = x.id + 1   -- `X` WALKS ITSELF
)

通過將下面的行放在 CTE 之後並執行來了解 CTE 的結果 - 查看 #s 10 和 11。

select * from cte order by id

查看 CTE 的結果後，刪除 CTE 的 select 語句，再次執行並替換為更新和 select 以查看更新後的數據。

UPDATE tbl_1min, cte SET tbl_1min.ema9 = cte.NewEma
WHERE cte.id = tbl_1min.id;

select * from tbl_1min;

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