How to set nested path values in the tree structure?

In our database, a tree structure is implemented like this:

name      path       pos_left     pos_right     pos_level

folder1              207          214           1
file1                208          209           2
file2                210          211           2
file3                212          213           2

I need to set the path’s values so that rows where pos_level is 2 would read like this: folder1/file1, folder1/file2, folder1/file3. So I need an SQL query which would take the name of the “parent” folder (but no parent_id is used in our system, only pos_left, pos_right, pos_level) concatenated with “/” and then with the entity’s name.

I already have the simple stuff:

UPDATE table SET path = name WHERE pos_level = 1

And I need this:

UPDATE table SET path = (code I don't know) WHERE pos_level = 2

Thank you!

Answer

Since you are using older version of MySQL recursion is out of scope. I have used self join to get the result you want. Please use your table name instead of treestructure in the query:

Schema and insert statements:

 create table treestructure(name varchar(50),   path varchar(100), pos_left int,pos_right int,pos_level int)
 
 insert into treestructure(name, pos_left,pos_right ,pos_level ) values('folder1',   207,          214,           1);
 insert into treestructure(name, pos_left,pos_right ,pos_level ) values('file1',     208,          209,           2);
 insert into treestructure(name, pos_left,pos_right ,pos_level ) values('file2',     210,          211,           2);
 insert into treestructure(name, pos_left,pos_right ,pos_level ) values('file3',     212,          213,           2);

Update query:

 update treestructure t
 left join treestructure ts on ts.pos_level=t.pos_level-1 and t.pos_left between ts.pos_left and ts.pos_right  and  t.pos_right between ts.pos_left and ts.pos_right
 set t.path=(case when t.pos_level=1 then t.name else concat(ts.name,'/',t.name) end);

Select Query:

 select*from treestructure;

Output after update:

name	path	pos_left	pos_right	pos_level
folder1	folder1	207	214	1
file1	folder1/file1	208	209	2
file2	folder1/file2	210	211	2
file3	folder1/file3	212	213	2

db<>fiddle here