The update query below shows that the PICTURE column is updated by looking up the same ID value in CATEGORY_ID column in table Categories_Test and Categories. If you need to update multiple columns simultaneously, use comma to separate each column after the SET keyword. Here we only want to update PICTURE column in Categories_Test table where the data in Category_Name column is Seafood in table Categories. Software and hardware names mentioned on this site are registered trademarks of their respective companies.

updating multiple tables in pl sql-18

After each exercise, we provide the solution so you can check your answer.

I can't load the relation data directly into product.

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.

There's a chance of the parent not existing before the child. 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. 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; 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.Sometimes, solutions are easier than thought, and I believe this is the case.UPDATE summary_data SET current_category = (SELECT category_id FROM products WHERE products.product_id = summary_data.product_id) WHERE EXISTS (SELECT category_id FROM products WHERE products.product_id = summary_data.product_id); If you want to test your skills using the SQL UPDATE statement, try some of our practice exercises.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 : 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 : 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.These exercises allow you to try out your skills with the UPDATE statement.