title: Updating two Tables Simultaneously - Oracle
body: <p>Say I have two tables such as:</p>
  
  <pre><code>Product
  prd_id   prd_name   parent_prd_id  ...
  123      Foo        &lt;null&gt;
  456      Bar        &lt;null&gt;
  789      Baz        &lt;null&gt;
  
  
  Product_Parent_Relation_Batch
  prd_id   prd_parent_id   processed
  555      888             T
  123      789             F
  456      789             F
  </code></pre>
  
  <p>I can't load the relation data directly into product. There's a chance of the parent not existing before the child. Poor design or not, that's the way it is. To update the product table, I'd do a correlated update like:</p>
  
  <p><a href="https://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-data-from-another-table">Oracle SQL: Update a table with data from another table</a></p>
  
  <p>I want to populate products 123 and 456 with the parent id 789 and set 123 and 456 to processed = 'T' in the product parent relation batch table. Seems like I can only do one. </p>
  
  <p>Am I forced to do this in an application, where I query for all non-processed batch records that have a matching prd_id with an existing product record, execute one individual update statement for the product table and another for the relation batch table, for all applicable records?</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE sync_prd_with_parent
  IS
     cursor c1 is
       select prd_id, parent_prd_id
       from product_parent_relation_batch
       inner join product on product_parent_relation_batch.prd_id = product.prd_id
       where product_parent_relation_batch.processed = 'F';
  BEGIN
     FOR rec in c1
     LOOP
        UPDATE product SET parent_prd_id = rec.parent_prd_id WHERE prd_id = rec.prd_id;
        UPDATE product_parent_relation_batch SET processed = 'T' WHERE product_parent_relation_batch.prd_id= rec.prd_id;
     END LOOP;
  END;
  </code></pre>
  
  <p>I'm going to settle for the above PL/SQL program unless a better suggestion is posted.</p>