Reputation: 189
Updating two Tables Simultaneously - Oracle
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.
Upvotes: 6
Views: 53137
Answers (4)
Reputation: 1003
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;
Upvotes: 2
Reputation: 1003
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>
Upvotes: 0
Reputation: 189
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.
Upvotes: 4
Reputation: 5288
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.
Upvotes: 3
Related Questions
- Oracle - Update from another table
- Update query with 2 tables
- How can we update in more than 1 table?
- ORACLE UPDATE SAME TABLE
- Updating 2 tables at once in Oracle
- How do you use an update between two tables in Oracle SQL
- How to update a table from two select queries?
- Oracle update on two tables
- update a table from another table using oracle db
- Insert and update simultaneously