Updating two Tables Simultaneously - Oracle

oracle update multiple columns from another table
update query in oracle using two tables
sql update multiple tables
oracle sql update multiple columns
oracle update statement with join
oracle merge update multiple tables
oracle update case statement multiple tables
oracle update from another table

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.

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.

Updating two tables in single query, Hi all, My requirement is to update two tables in single query. I have tried using below query but its showing error. SELECT * FROM DEPT  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.

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.

4 ways to do multiple table update in Oracle (what works and what not), Update data in two columns in table A based on a common column in table B. If you need to update multiple columns simultaneously, use comma to separate each  In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using the same id? So the end result I would get is. Table 1: id name desc ----- 1 x 123 2 y 345 3 c adf Question is taken from update one table with data from another, but specifically for oracle SQL.

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;

Ask TOM "Performing updates to multiple columns from two , Hi, I have two tables. Table A and Table B. I'm trying to perform an update on 1 column from table A and 2 columns from table B. I can of course  Updating two tables in single query. Is there any way to update multiple tables in single query. If possible please post it with an example. oracle, triggers

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> 

UPDATE multiple tables in a single statement, Hi can anyone give me an example to update 2 or more tables in a On 3/13/08, saravanan p via oracle-sql-l <oracle-sql-l@groups.ittoolbox.com> of an employee is updated than simultaneously the dept of employee  Your example is somewhat confusing -- you ask "update column a1 in table a where data in column b1 in table b" but your update shows you updating column b1 in table B with some data from table a. Additionally -- given the way the where and set clauses are CODED in the above -- it would succeed.

SQL: UPDATE Statement, The SQL UPDATE statement is used to update existing records in the tables. UPDATE statement when updating multiple tables (not permitted in Oracle) is: how to use the UPDATE statement to update more than one column value at once. You can update with a join if you only affect one table like this: UPDATE table1 SET table1.name = table2.name FROM table1, table2 WHERE table1.id = table2.id AND table2.foobar ='stuff' But you are trying to affect multiple tables with an update statement that joins on multiple tables. That is not possible.

SQL: how to update two (or more) tables at the same time, This is the case of a simple task like updating two related tables with just one SQL query. Suppose we have two related tables. The first  The syntax for the Oracle UPDATE statement when updating one table with data from another table is: UPDATE table1 SET column1 = (SELECT expression1 FROM table2 WHERE conditions) [WHERE conditions];

The Basics of Oracle UPDATE Statement: How to Change Existing , To changes existing values in a table, you use the following Oracle UPDATE If you update more than two columns, you separate each expression column  Enter search criteria to populate the item list. Select the items whose field values you want to change. Select Multi-item Update in the Item Action group box and click Go to access the Multi-item Update page. Select the fields whose values you want to change for the selected items.

Comments
  • This is a cool alternative to using a cursor - does the updates in batches instead of a loop of 1-record updates.
  • Considered it, but I'm going with the cursor because it takes a snapshot of the two tables and updates data without race conditions. I think if a record came in to product_parent_relation_batch while the update product in lines 2-12 was running, product would never get its parent relation because it would then be marked as updated. This approach could be feasible with other use cases.
  • True. I verified that the error mentioned will happen. But it's simple to fix it. Just make the transaction Isolation Level serializeble and it's all safe. Will post a complete answer below.