PL/SQL Multi-Table Association update

Suppose there are two tables A and B,a table fields a,b,c,d,b table fields b,e,f, the association condition of two tables is field B, now want to make a data patch, to patch the value of field E in Table B to the field C of table A.

There are two ways to do this:

1

Update Set A.C=(selectfrom where b.b=a.b)whereexists (Select1fromwhere b.b=a.b);

2

 into ausing B  on (a.b=b.b)  when  Then Update Set A.C=B.E;

The above two methods can be implemented to update the multi-table junction, where the B table can also be a subquery, view.

Merge into is a syntax added after Oracle 9i that can implement Update/insert functionality (satisfying conditional updates, not satisfying conditional insertions), and is more efficient because the merge requires only one full table scan, but merge into uses caution, You must understand its usage to be assured of use, otherwise there may be problems.

The above example can not only update a single field, but also update multiple fields as follows:

1

Update Set A.C=(selectfromwhere b.b=a.b),             A.D=(  Selectfromwhere b.b=a.b)whereexists( Select 1  from where b.b=a.b);

2

 into ausing B  on (a.b=b.b)  when  Then Update Set A.C=b.e,                             a.d=b.f;

There are more complicated situations where multiple fields are updated from different tables, such as the D field in table A is derived from a field in the C table, and the individual feels that this situation does not need to be written in a SQL text, divided into two SQL lines.

On the internet to see sucha word, "in a merge statement that has both the INSERT and update syntax, the total number of insert/update records is the number of records ALIAS2 in the using statement." personally feel wrong, the premise of this conclusion should be the source table and the target table is associated with the field is a unique constraint, in my case, the B field as the primary key, and the B field of a table can be repeated, if the table B has 4 data, a table in the corresponding B table in each record has two data, Then the number of records updated is actually 8, not to mention the INSERT statement with not Matche.

If you have any feeling wrong, please point out, thank you.

PL/SQL Multi-Table Association update