Updating oracle table from a join
Source: Host : host01 Non-CDB : orcl having ASM storage Destination: Host : host02 Container Database : destcdb Pluggable database : orclclone having file system storage Let’s confirm that our source database orcl is a Non-CDB: select name from [email protected]_link; NAME -------------------------------------------------------------------------------- DATA/ORCL/DATAFILE/system.258.904565437 DATA/ORCL/DATAFILE/sysaux.257.904565371 DATA/ORCL/DATAFILE/undotbs1.260.904565513 DATA/ORCL/DATAFILE/example.266.904565615 DATA/ORCL/DATAFILE/users.259.904565511 Although the process is quite simple, it may not be feasible for large databases or situations involving slow or unreliable network links since it depends on transporting the data over a database link.Hence, you must consider the size of your source database and the speed of your internet connection in order to decide if it is a feasible migration approach in your case.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.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.In the initial release of Oracle 12c (126.96.36.199c), a non-Container Database (non-CDB) could be converted to a pluggable database (PDB) using any of the following methods: Using DBMS_PDB Using datapump (expdb, impdp) Using Golden Gate replication The 188.8.131.52 patchset has introduced the ability to clone a remote non-CDB as a PDB directly over the network.
On the second pass, I need to do an update to this table to populate the summation values from my transaction tables.The Oracle UPDATE statement is used to update existing records in a table in an Oracle database.There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.I can’t think of a good reason why these extra privileges are needed. You should not build a system that allows end-users to connect to the database as the data owner.But it’s still quite common for development work to take place in an environment that is a little lax about this rule, with developers connecting as the data owner to write code.
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; One is physically restricted from updating multiple tables in the same query.