title: Updating child rows based on parent values with Oracle sql
body: <p>I have 2 tables in Oracle with structures similar to these:</p>
  
  <p><strong>family</strong> table</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>flight</strong> table</p>
  
  <pre><code>id   family_id   flight
  -----------------------
  
  1      1         1000
  2      3         2000
  </code></pre>
  
  <p>Here rows with id  4,5 in family table are child of Row 3 and Row 2 is child of 1.</p>
  
  <p>Now, I need to write a oracle update sql to not only update the corresponding parent row with flight and change the flag to Y but child rows also should be updated accordingly. If there is no corresponding flight assignment for family in flight table then rows 6,7 should remain as such.</p>
  
  <p>Doing parent update and finding child for a parent easy. But is it possible to do all updates in one sql?</p>
  
  <p>Thanks.</p>
  
  <p><strong>Update</strong></p>
  
  <pre><code>update family fm set (flag, flight) = 
  (
    select 'Y', fl.flight  from flight fl where fm.flag &lt;&gt; '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 );
  </code></pre>
  
  <p>This works !!</p>