INSERT INTO query WITH RECURSIVE query

August 25, 2022

I have created a WITH RECURSIVE function and would like to use it with an INSERT INTO function so that the results insert into the path column.

category_id	category_name	parent_id
1	Root	0
2	Fishing	1
3	Sea	2
4	Lures	3
7	Coarse	2
8	Lures	7
9	Fly	2

I have tried the following code but the results do not fall in line with the table.

INSERT INTO categories (path)

WITH RECURSIVE
cte AS ( SELECT category_id, category_name, parent_id, category_name path
         FROM categories
         WHERE parent_id = 0
       UNION ALL
         SELECT c.category_id, c.category_name, c.parent_id, CONCAT(cte.path, '/', c.category_name)
         FROM categories c
         JOIN cte ON cte.category_id = c.parent_id 
       )
SELECT path FROM cte;

category_id	category_name	parent_id	path
1	Root	0
2	Fishing	1
3	Sea	2
4	Lures	3
7	Coarse	2
8	Lures	7
9	Fly	2
null	null	null	Root
null	null	null	Root/Fishing
null	null	null	Root/Fishing/Sea
null	null	null	Root/Fishing/Coarse
null	null	null	Root/Fishing/Fly
null	null	null	Root/Fishing/Sea/Lures
null	null	null	Root/Fishing/Coarse/Lures

I have the code in a db fiddle
db fiddle

>Solution :

Here is my executed code.

UPDATE categories ca  
       inner join ( WITH RECURSIVE
cte AS ( SELECT category_id, category_name, parent_id, category_name path
         FROM categories
         WHERE parent_id = 0
       UNION ALL
         SELECT c.category_id, c.category_name, c.parent_id, CONCAT(cte.path, '/', c.category_name)
         FROM categories c
         JOIN cte ON cte.category_id = c.parent_id 
       )
                   select * from cte
       ) t on ca.category_id = t.category_id 
SET ca.path = t.path ;

SELECT * FROM categories;