Update Column from Recursive CTE (MySQL/MariaDB) I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table.
My code is below- basically I want to:
UPDATE tbl_1min
SET tbl_1min.ema9 = x.NewEma
WHERE x.id = tbl_1min.id

Can anyone help? I'd really appreciate it!
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
;
You can use any query typo with the created (with statements
The update can be de by joining the original with the cnstructed


CREATE tABLE tbl_1min(id int, close int,ema9 varchar(30))




INSERT INTO tbl_1min VALUES (1,1,'test')




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
    )
UPDATE tbl_1min t1
        INNER JOIN
    x ON t1.id = x.id 
SET t1.ema9 = x.NewEma;




SELECT * FROM tbl_1min


id | close | ema9
-: | ----: | :---
 1 |     1 | test


db&lt;&gt;fiddle here