Oracle merge into... USING two table join operations (high efficiency), mergeinto... using
When the data volume is small, you can use subqueries to associate two tables. However, when the data volume is large, the subquery efficiency is too low (because it is a single comparison)
For example:
update person1 p1 set p1.p_name=(select p_name from person2 where p1.p_id=p2.p_id) where p1.add_date>to_date('2014-09-01','yyyy-mm-dd')
Using merge into... USING for Two-table join operations (adding, deleting, and modifying) is highly efficient.
MERGE INTO person1 p1 USING (select p_id,p_name from person2 where use='952701' ) p2 ON ( p1.p_id=p2.p_id ) WHEN MATCHED THEN UPDATE SET p1.p_name = p2.p_name where p1.add_date>to_date('2014-09-01','yyyy-mm-dd') commit;
[Oracle] update operations related to two tables
(A. z = B. z) the number is not unique. Which one do you want to use?
Give you the largest one:
Update a set a. x =
(Select max (B. x) from B where a. z = B. z)
Where y = 3
;
The two tables have a large amount of data. You can only create an index on the z field of the two tables to speed up the process.
ORACLE usage of merge
After when matched then and when not matched then, only insert () values or update set ....,...., conditions cannot be attached, and other statements following then cannot be used. In addition, it is noted that the B domain is only useful for queries in using. If you want to use it, you must use M in Using () m.