INSERT INTO query WITH RECURSIVE query

Question

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

INSERT creates new rows. You probably want to use UPDATE.

Bill Karwin
– Bill Karwin

2022-08-25 20:17:52 +00:00
Commented Aug 25, 2022 at 20:17 — Bill Karwin
– Bill Karwin, Commented Aug 25, 2022 at 20:17

Farshid Shekari · Accepted Answer · 2022-08-25 20:42:14Z

1

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;

edited Aug 25, 2022 at 20:42

answered Aug 25, 2022 at 20:30

Farshid Shekari

2,4394 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT INTO query WITH RECURSIVE query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related