Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as
    select 1 as id, NULL as parent_id, 100 as value_id from dual union all
    select 2 as id, 1    as parent_id, NULL as value_id from dual union all
    select 3 as id, 2    as parent_id, 200 as value_id from dual union all
    select 4 as id, 3    as parent_id, NULL as value_id from dual union all
    select 5 as id, 1    as parent_id, 300 as value_id from dual union all
    select 6 as id, 2    as parent_id, NULL as value_id from dual union all
    select 7 as id, 6    as parent_id, 400 as value_id from dual union all
    select 8 as id, 7    as parent_id, 500 as value_id from dual
    
8 rows affected

select *
from t


ID
PARENT_ID
VALUE_ID


1
null
100

2
1
null

3
2
200

4
3
null

5
1
300

6
2
null

7
6
400

8
7
500


with cte(id, value_id, parent_value_id) as (
      select id, value_id, value_id as parent_value_id
      from t
      where value_id is not null
      union all
      select t.id, t.value_id, cte.parent_value_id
      from cte join
           t
           on t.parent_id = cte.id
      where t.value_id is null
     )
select *
from cte
order by id


ID
VALUE_ID
PARENT_VALUE_ID


1
100
100

2
null
100

3
200
200

4
null
200

5
300
300

6
null
100

7
400
400

8
500
500

ID	VALUE_ID	PARENT_VALUE_ID
1	100	100
2	null	100
3	200	200
4	null	200
5	300	300
6	null	100
7	400	400
8	500	500