Update с использованием рекурсивного запроса

CREATE TABLE geo (
    id int not null primary key, 
    parent_id int references geo(id),  
    name varchar(1000),
     level int,
     hier varchar(1000),
    root int,
    new_root bool    
);
 
INSERT INTO geo 
(id, parent_id, name, level, hier,root, new_root) 
VALUES 
(1, null, 'Планета Земля', null, null,null, null),
(2, 1, 'Континент Евразия', null, null,null, null),
(3, 1, 'Континент Северная Америка', null, null,null, null),
(4, 2, 'Европа', null, null,null, null),
(5, 4, 'Россия', null, null,null, true),
(6, 4, 'Германия', null, null,null, null),
(7, 5, 'Москва', null, null,null, null),
(8, 5, 'Санкт-Петербург', null, null,null, null),
(9, 6, 'Берлин', null, null,null, null);

WITH RECURSIVE r AS (
   SELECT id, parent_id, name, 2 AS level, null AS hier, 1 as root
   FROM geo
   WHERE parent_id = 1
 
   UNION ALL
 
   SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level,  case when r.hier is NULL then (select name from geo tmp where tmp.id=geo.parent_id limit 1) else  r.hier::text || '#' || (select name from geo tmp where tmp.id=geo.parent_id limit 1) end AS hier,
CASE WHEN (SELECT new_root FROM geo tmp WHERE tmp.id=geo.parent_id LIMIT 1) IS true 
   THEN (SELECT id FROM geo tmp WHERE tmp.id=geo.parent_id LIMIT 1)
   ELSE r.root END AS root
   FROM geo inner JOIN r ON (geo.parent_id = r.id)
),
T AS(
select id,  parent_id, name, 1 AS level, null::text AS hier, id as root
from geo
where id = 1
union
SELECT * FROM r
)
Update geo
set(level, hier, root) = (T.level, T.hier, T.root)
from T
where geo.id=T.id;
select *
from geo