Overwriting nulls with parent data in a hierarchical query

Question

I have a table with the following format:

pk    fk     parent
===== ====== ========
001   23     000
002   null   001
003   46     001
004   12     000
005   null   004
006   null   005
=====================

pk is the primary key for each row, fk is a foreign key to another table I'll need to join later, and parent is the record's hierarchical parent. I can create a hierarchical query on this data with

select 
    lpad(' ',2*level)||pk "primary_key",
    fk "foreign_key",
    sys_connect_by_path(pk,'/') "path"
from example_table
connect by prior pk = parent
;

My question is how do I overwrite a row's null foreign key with that of the lowest ancestor without a null? My intended output in this scenario is

pk        fk   parent
========= ==== ======
001       23   000
  002     23   001
  003     46   001
004       12   000
  005     12   004
    006   12   005
====================

(primary keys padded to show hierarchical structure.)

Answer 1

You could express this with a standard recursive common table expression:

with cte (pk, fk, parent, lvl, pat) as (
    select to_char(pk), fk, parent, 0, to_char(pk) from mytable where parent = 0
    union all
    select lpad(' ', 2 * (lvl + 1)) || t.pk, coalesce(t.fk, c.fk), t.parent, c.lvl + 1, c.pat || '/' || t.pk
    from cte c
    inner join mytable t on t.parent = c.pk
)
select pk, fk, parent from cte order by pat

Demo on DB Fiddlde:

PK    | FK | PARENT
:---- | -: | -----:
1     | 23 |      0
  2   | 23 |      1
  3   | 46 |      1
4     | 12 |      0
  5   | 12 |      4
    6 | 12 |      5