Skip to content
Advertisement

INSERT INTO query WITH RECURSIVE query

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 path
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

Advertisement

Answer

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;
Advertisement