title: Reorder Parent-child relationship in same table - Oracle SQL
body: <p>I have below data in a table named CPGRPRULREL. </p>
  
  <pre><code>CPGRPRULREL_NUM | CPGRPRULREL_NUM_PRIOR | STATUS     | CNDQUALSRC_CD | CNDQUALTYP_CD | CPGRP_NUM
  --------------------------------------------------------------------------------------------------
  41292           | 41289                 | Active     | PRODCNT       | PRODCNTUNQ    | 
  41289           | 41286                 | Superseded | OTHERPG       |               | 118729
  41286           | 41283                 | Superseded | SUBM          | VOLUME        | 
  41283           | 41280                 | Superseded | OTHERPG       |               | 118729
  41280           | 41277                 | Superseded | OTHERPG       |               | 118729
  41277           | null                  | Superseded | PRODCNT       | PRODCNTALL    | 
  </code></pre>
  
  <p>Here, CPGRPRULREL_NUM_PRIOR is a link to CPGRPRULREL_NUM (i.e. has foreign key constraint - kind of parent-child relationship).</p>
  
  <p>I want to delete all with CPGRP_NUM = 118729. Before that, I need to update the values of CPGRPRULREL_NUM_PRIOR for remaining rows so that proper linking remains intact.</p>
  
  <p>I need update query which will only update rows having CPGRP_NUM &lt;> 118729 and have below data after running update. </p>
  
  <pre><code>CPGRPRULREL_NUM | CPGRPRULREL_NUM_PRIOR | STATUS     | CNDQUALSRC_CD | CNDQUALTYP_CD | CPGRP_NUM
  --------------------------------------------------------------------------------------------------
  41292           | 41286                 | Active     | PRODCNT       | PRODCNTUNQ    | 
  41289           | 41286                 | Superseded | OTHERPG       |               | 118729
  41286           | 41277                 | Superseded | SUBM          | VOLUME        | 
  41283           | 41280                 | Superseded | OTHERPG       |               | 118729
  41280           | 41277                 | Superseded | OTHERPG       |               | 118729
  41277           | null                  | Superseded | PRODCNT       | PRODCNTALL    |            
  </code></pre>
  
  <p>Note that 2 rows with CPGRPRULREL_NUM 41292 and 41286 has CPGRPRULREL_NUM_PRIOR updated.</p>