Hierarchical multiple update query on one table - oracle

Question

I have a table person with these columns, dependent on each other.

person_id, related_person_id, beginner_related_person_id 

Example of my data

person_id related_person_id beginner_related_person_id 
1         null              null
2         1                 null
3         2                 null
4         null              null
5         4                 null 
6         null              null

This column beginner_related_person_id is new and for now null.

I have to update this row with values from person_id, but the root person_id.

person_id related_person_id beginner_related_person_id
1         null              null
2         1                 1
3         2                 1
4         null              null
5         4                 4 
6         null              null

I have started to write my query but need a hint.

update person
set beginner_related_person_id = person_id
where person_id in (select  distinct person_id from person start with  related_person_id in (select person_id from person)
  connect by prior beginner_person_id = person_id);

Should I use loops? Or something else?

Answer 1

You can use a MERGE statement to do this pretty easily.

merge into person using (
select person_id, 
       related_person_id, 
       nullif(connect_by_root person_id, person_id) new_beginner_related_id
from   person
start with 
   related_person_id is null
connect by  
   related_person_id = prior person_id ) u
on ( t.person_id = u.person_id )
when matched then 
  update set 
     t.beginner_related_person_id = u.new_beginner_related_id;