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

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.

Answer 2

Reproducing the error caused by concurrent sessions.

First session executes the Update on Product:

08/12/2015 17:46:54:SQL> -- session 1
08/12/2015 17:47:12:SQL> BEGIN
  2    UPDATE product pr
  3       SET parent_prd_id =
  4           (SELECT b.prd_parent_id
  5              FROM product_parent_relation_batch b
  6             INNER JOIN product p ON b.prd_id = p.prd_id
  7             WHERE b.processed = 'F'
  8               AND pr.prd_id = p.prd_id)
  9     WHERE prd_id in (SELECT p.prd_id
 10                        FROM product_parent_relation_batch b
 11                       INNER JOIN product p ON b.prd_id = p.prd_id
 12                       WHERE b.processed = 'F');
 13  END;
 14  /

Procedimento PL/SQL concluído com sucesso.

Before the 2nd update happens a different session inserts new rows:

08/12/2015 17:47:31:SQL> -- session 2
08/12/2015 17:47:31:SQL> INSERT INTO product
  2    VALUES (990, 'New', null);

1 linha criada.

08/12/2015 17:47:31:SQL> INSERT INTO product_parent_relation_batch
  2    VALUES (990, 789, 'F');

1 linha criada.

08/12/2015 17:47:31:SQL> 
08/12/2015 17:47:31:SQL> commit;

Commit concluído.

Then, with those new commited rows, our first transaction Updates the Batch table:

    08/12/2015 17:47:50:SQL> --- continues
    08/12/2015 17:47:50:SQL> UPDATE product_parent_relation_batch pb
      2     SET processed = 'T'
      3   WHERE pb.prd_id IN (SELECT b.prd_id
      4                         FROM product_parent_relation_batch b
      5                        INNER JOIN product p ON b.prd_id = p.prd_id
      6                        WHERE b.processed = 'F'
      7                              AND pb.prd_id = p.prd_id);

    3 linhas atualizadas.

    08/12/2015 17:47:50:SQL> SELECT *
      2    FROM product_parent_relation_batch b
      3   INNER JOIN product p
      4      ON b.prd_id = p.prd_id
      5   WHERE p.prd_id = 990;

        PRD_ID PRD_PARENT_ID P     PRD_ID PRD PARENT_PRD_ID                                                                                                                                                                                                                                                                                                                                                                                                                                                             
    ---------- ------------- - ---------- --- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                             
           990           789 T        990 New                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

    08/12/2015 17:47:50:SQL> COMMIT;

    Commit concluído.

Notice 3 rows are updated. The error you mentioned is displayed by checking that "New" row that now has 'T' instead of 'F'.

Now let's try it changing it to Serializable Isolation Level:

08/12/2015 17:51:08:SQL> -- session 1
08/12/2015 17:51:24:SQL> BEGIN
  2    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  3    UPDATE product pr
  4       SET parent_prd_id =
  5           (SELECT b.prd_parent_id
  6              FROM product_parent_relation_batch b
  7             INNER JOIN product p ON b.prd_id = p.prd_id
  8             WHERE b.processed = 'F'
  9               AND pr.prd_id = p.prd_id)
 10     WHERE prd_id in (SELECT p.prd_id
 11                        FROM product_parent_relation_batch b
 12                       INNER JOIN product p ON b.prd_id = p.prd_id
 13                       WHERE b.processed = 'F');
 14  END;
 15  /

Procedimento PL/SQL concluído com sucesso.

Then concurrent insert:

08/12/2015 17:50:59:SQL> -- session 2
08/12/2015 17:51:46:SQL> INSERT INTO product
  2    VALUES (990, 'New', null);

1 linha criada.

08/12/2015 17:51:46:SQL> INSERT INTO product_parent_relation_batch
  2    VALUES (990, 789, 'F');

1 linha criada.

08/12/2015 17:51:46:SQL> 
08/12/2015 17:51:46:SQL> commit;

Commit concluído.

And finally the 2nd update:

08/12/2015 17:51:24:SQL> --- continues
08/12/2015 17:52:16:SQL> UPDATE product_parent_relation_batch pb
  2     SET processed = 'T'
  3   WHERE pb.prd_id IN (SELECT b.prd_id
  4                         FROM product_parent_relation_batch b
  5                        INNER JOIN product p ON b.prd_id = p.prd_id
  6                        WHERE b.processed = 'F'
  7                              AND pb.prd_id = p.prd_id);

2 linhas atualizadas.

08/12/2015 17:52:16:SQL> SELECT *
  2    FROM product_parent_relation_batch b
  3   INNER JOIN product p
  4      ON b.prd_id = p.prd_id
  5   WHERE p.prd_id = 990;

não há linhas selecionadas

08/12/2015 17:52:16:SQL> COMMIT;

Commit concluído.

08/12/2015 17:52:16:SQL> SELECT *
  2    FROM product_parent_relation_batch b
  3   INNER JOIN product p
  4      ON b.prd_id = p.prd_id
  5   WHERE p.prd_id = 990;

    PRD_ID PRD_PARENT_ID P     PRD_ID PRD PARENT_PRD_ID                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------- ------------- - ---------- --- -------------                                                                                                                                                                                                                                                                                                                                                                                                                                                             
       990           789 F        990 New                                                                                                                                                                            

The new row is untouched, because the Serializable isolation level makes it a snapshot at the beginning of the transaction.

The correct version would be similar to this:

BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  UPDATE product pr
     SET parent_prd_id =
         (SELECT b.prd_parent_id
            FROM product_parent_relation_batch b
           INNER JOIN product p ON b.prd_id = p.prd_id
           WHERE b.processed = 'F'
             AND pr.prd_id = p.prd_id)
   WHERE prd_id in (SELECT p.prd_id
                      FROM product_parent_relation_batch b
                     INNER JOIN product p ON b.prd_id = p.prd_id
                     WHERE b.processed = 'F');
  UPDATE product_parent_relation_batch pb
     SET processed = 'T'
   WHERE pb.prd_id IN (SELECT b.prd_id
                         FROM product_parent_relation_batch b
                        INNER JOIN product p ON b.prd_id = p.prd_id
                        WHERE b.processed = 'F'
                              AND pb.prd_id = p.prd_id);
  COMMIT;
END;

Answer 3

You could forego the cursor and go straight for the 2 updates as follows:

    SQL> create table product (prd_id,
  2                        prd_name,
  3                        parent_prd_id)
  4  as
  5  select 123, 'Foo', cast(null as number) from dual union all
  6  select 456, 'Bar', null from dual union all
  7  select 789, 'Baz', null from dual;
Table created
SQL> create table product_parent_relation_batch
  2                       (prd_id,
  3                        prd_parent_id,
  4                        processed)
  5  as
  6  select 555, 888, 'T' from dual union all
  7  select 123, 789, 'F' from dual union all
  8  select 456, 789, 'F' from dual;
Table created
SQL> SELECT p.prd_id, b.prd_id, prd_parent_id
  2    FROM product_parent_relation_batch b
  3   INNER JOIN product p
  4      ON b.prd_id = p.prd_id
  5   WHERE b.processed = 'F'
SQL> BEGIN
  2    UPDATE product pr
  3       SET parent_prd_id =
  4           (SELECT b.prd_parent_id
  5              FROM product_parent_relation_batch b
  6             INNER JOIN product p ON b.prd_id = p.prd_id
  7             WHERE b.processed = 'F'
  8               AND pr.prd_id = p.prd_id)
  9     WHERE prd_id in (SELECT p.prd_id
 10                        FROM product_parent_relation_batch b
 11                       INNER JOIN product p ON b.prd_id = p.prd_id
 12                       WHERE b.processed = 'F');
 13    UPDATE product_parent_relation_batch pb
 14       SET processed = 'T'
 15     WHERE pb.prd_id IN (SELECT b.prd_id
 16                           FROM product_parent_relation_batch b
 17                          INNER JOIN product p ON b.prd_id = p.prd_id
 18                          WHERE b.processed = 'F'
 19                                AND pb.prd_id = p.prd_id);
 20    COMMIT;
 21  END;
 22  /
PL/SQL procedure successfully completed
SQL> SELECT * FROM product_parent_relation_batch;
    PRD_ID PRD_PARENT_ID PROCESSED
---------- ------------- ---------
       555           888 T
       123           789 T
       456           789 T
SQL> SELECT * FROM product;
    PRD_ID PRD_NAME PARENT_PRD_ID
---------- -------- -------------
       123 Foo                789
       456 Bar                789
       789 Baz      

SQL> 

Answer 4

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.