Updating two Tables Simultaneously - Oracle

Question

Say I have two tables such as:

Product
prd_id   prd_name   parent_prd_id  ...
123      Foo        <null>
456      Bar        <null>
789      Baz        <null>


Product_Parent_Relation_Batch
prd_id   prd_parent_id   processed
555      888             T
123      789             F
456      789             F

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:

Oracle SQL: Update a table with data from another table

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.

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?

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;

I'm going to settle for the above PL/SQL program unless a better suggestion is posted.

Answer 1

One is physically restricted from updating multiple tables in the same query.

A working solution for this kind of scenario is to create an application - PL/SQL or otherwise, to grab information for both tables you need to update, iterate through the results, and update the tables in individual statements in each iteration.

Answer 2

There is no way how to do that in a single statement. Even when using update-able joins, only one table can be subject of the change.