Updating child rows based on parent values ​​with Oracle sql

I have 2 tables in Oracle with structures like these:

family table

id member parent_id  flag flight
---------------------------------
1   A     null       N    null
2   B      1         N    null
3   C     null       N    null
4   D      3         N    null
5   E      3         N    null
6   F     null       N    null
7   G      6         N    null

      

        
        
        
      

    

flight table

id   family_id   flight
-----------------------

1      1         1000
2      3         2000

      

        
        
        
      

    

Here, rows with id 4,5 in the family table are children of row 3 and row 2 is child of 1.

Now I need to write sql update update sql to not only update the corresponding parent row with flight and change the flag to Y, but also update the child rows accordingly. If in flight there is no corresponding flight assignment for the family, then lines 6,7 should remain so.

Performing a parent update and finding a parent for the parent. But is it possible to do all updates in one sql?

Thank.

Update

update family fm set (flag, flight) = 
(
  select 'Y', fl.flight  from flight fl where fm.flag <> 'Y' and
(
  (fl.parent_id=fm.id and fm.parent_id is null )
 or fm.parent_id=fl.parent_id
)
)
where exists (select 1 from flight fl where fm.id=fl.parent_id or fl.parent_id=fm.parent_id );

      

        
        
        
      

    

It works!!