Update rows based on a recursive CTE query result

Question

This query shows all lines recursively based on a uuid / parentUUID relationship:

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

However if I need to update lines with provided id (as follows) the query fails:

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

(Error is "You have an error near...")

Any suggestion is appreciated.

• Bill Karwin: Are you actually using MariaDB? It only supports SELECT after a CTE, not UPDATE.

Answer 1

You must declare your CTE inside the update statement. Here is a trivial example:

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

Answer 2

Here is what I've found and that works both in MariaDB and 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
)