0

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.

1
  • Are you actually using MariaDB? It only supports SELECT after a CTE, not UPDATE. Commented Sep 3, 2021 at 20:36

2 Answers 2

2

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
0

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
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.