根據遞歸 CTE 查詢結果更新行

此查詢基於 uuid / parentUUID 關係遞歸地顯示所有行：

WITH RECURSIVE files_paths (id, parent) AS
(
 SELECT uuid, parentuuid
   FROM core_data
   WHERE uuid = '2c828bbb-71c4-4a00-8d54-d1a4575ec3ef'
 UNION ALL
 SELECT e.uuid, e.parentuuid
   FROM files_paths AS ep JOIN core_data AS e
     ON ep.id = e.parentuuid
)
SELECT id FROM files_paths

但是，如果我需要使用提供的 id 更新行（如下所示），則查詢失敗：

UPDATE `core_data` SET `attrib` = 'test' WHERE `uuid` IN (SELECT id FROM files_paths)

（錯誤是“您在…附近有錯誤”）

任何建議表示讚賞。

您必須在更新語句中聲明您的 CTE。這是一個簡單的例子：

create table cd 
( id int not null primary key
, parent_id int references cd (id)
, visited int default 0 not null
);

insert into cd (id, parent_id) values (1,null),(2,1),(3,1),(4,2);

-- no nodes visited
select * from cd;

id  parent_id   visited
1       0
2   1   0
3   1   0
4   2   0

with recursive cte (i,p) as ( 
   select id, parent_id from cd where id = 2 
   union all 
   select id, parent_id 
   from cte join cd on cd.parent_id = cte.i
)
update cd set visited = 1 where id in (select i from cte);

select * from cd;

id  parent_id   visited
1       0
2   1   1
3   1   0
4   2   1

這是我發現的，適用於 MariaDB 和 MySQL：

UPDATE `core_data` SET `attrib` = 'test' WHERE `uuid` IN (
 WITH RECURSIVE files_paths (id, parent) AS
 (
   SELECT uuid, parentuuid
     FROM core_data
     WHERE uuid = '2c828bbb-71c4-4a00-8d54-d1a4575ec3ef'
   UNION ALL
   SELECT e.uuid, e.parentuuid
     FROM files_paths AS ep JOIN core_data AS e
       ON ep.id = e.parentuuid
 )
 SELECT id FROM files_paths
)

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