Syntax __oracle for Oracle update multiple tables

Understanding of the syntax and performance analysis of ORACLE UPDATE statements:

For convenience, the following simple models are established and some test data is constructed:
In the BSS of a business admissibility subsystem,
--Customer Information sheet

CREATE TABLE Customers (customer_id number (8) is not null--customer marked City_name varchar2 (a) NOT null--the city Customer_type Ch AR (2) NOT NULL,--Customer type ...) Create unique index pk_customers on customers (CUSTOMER_ID)
For some reason, the information in the customer's city is not exactly accurate, but
Customer service in the CRM subsystem, through the active services to obtain part of the customer 20% of the location
City, and so you extract this information into a temporary table:
CREATE TABLE Tmp_cust_city (customer_id number (8) NOT NULL, Citye_name VARCHAR2 (a) not NULL, Customer_type char (2) is not n ULL)

1 The simplest form
--confirmed that all customer_id less than 1000 of the Customers table are ' Beijing '
Within the--1000 are the company to the country before the old customers in the city:

Update customers set City_name= ' Beijing ' where customer_id<1000
2 Two tables (multiple tables) association update--only the connection in the WHERE clause
--This time the extracted data are VIP, and include new, so by the way update the customer category
Update customers A--use alias set customer_type= ' 01 '--01 for vip,00 to normal where exists (select 1 from tmp_cust_city b where b.cust OMER_ID=A.CUSTOMER_ID)

3 Two tables (multiple tables) association update--the modified value is calculated from another table
Update customers A--use alias set city_name= (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id) where Exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id)

--Update over 2 values
Update customers A-use alias set (city_name,customer_type) = (select B.city_name,b.customer_type from tmp_cust_city b where B.) CUSTOMER_ID=A.CUSTOMER_ID) where exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id)

Notice in this statement that the
= (select B.city_name,b.customer_type
From Tmp_cust_city b
where b.customer_id=a.customer_id
)
And
(SELECT 1
From Tmp_cust_city b
where b.customer_id=a.customer_id
)
is a two independent subquery, see the execution plan know that the B table/index scanned 2;
If you discard the where condition, the table of a is all table by default
Update, but because (select B.city_name from Tmp_cust_city b where
where b.customer_id=a.customer_id)
It may not be possible to provide "enough" value because tmp_cust_city is only a subset of the customer's information,
Therefore, the error (if the specified column--city_name can be null is another matter):

01407, 00000, "Cannot update (%s) to NULL"
*cause:
*action:

An alternative approach may be to use:
Update customers A--use alias set CITY_NAME=NVL (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id) . city_name) or set CITY_NAME=NVL (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id), ' unknown '

--Of course it's not in line with business logic.

4) the above 3) in some cases, since the record of Table B is only the number of 20-30% in table A,
Considering the use of index in table A, using cursor may result in better performance than associated update:
Set serveroutput on DECLARE cursor city_cur be select Customer_id,city_name from Tmp_cust_city order by customer_id; Begin for My_cur in City_cur Loop update customers set city_name=my_cur.city_name where customer_id=my_cur.customer_id;

/** Here you can also submit a single/batch, to avoid the lock table situation **/
--If mod (city_cur%rowcount,10000) =0 Then
--Dbms_output.put_line ('----');
--commit;
--End If;
End Loop;
End

5 A special case of association update and its performance re-discussion
In Oracle's UPDATE statement syntax, in addition to the Update table, can also be a view, so there are the following 1 special cases:
Update (select A.city_name,b.city_name as New_name from customers A, tmp_cust_city b where b.customer_id=a.customer_id) s ET city_name=new_name

    This avoids 2 scans of table B or its index, provided that a (customer_id) b (customer_id) must be a unique index
    or primary Key Otherwise, error:
   
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
//*cause:an Attempt is made to insert or update columns's a join view which
//         map to a No n-key-preserved table.
//*action:modify the underlying base tables directly.

6 Oracle Another common error
    back to 3) situation, for some reason, tmp_cust_city customer_id is not the only index/primary key
     Update customers A--use alias set city_name= (select B.city_name from tmp_cust_city b where b.customer_id=a.custo mer_id where exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id)   

When for a given a.customer_id
(select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
If you return more than 1 articles, you will report the following error:

01427, 00000, "Single-row subquery returns more than one row"
*cause:
*action:

A comparatively simple approximation to the irresponsible procedure is
Update customers A--use alias set city_name= (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id)

How to understand the 01427 error, in a very complex multiple table connection Update statement, often because of ill-considered, this error,
As described above, a simpler approach would be to place a table in the value expression, using GROUP by and
Having words to view duplicate records
(Select B.customer_id,b.city_name,count (*)
From Tmp_cust_city b,customers A
where b.customer_id=a.customer_id
GROUP BY B.customer_id,b.city_name
Having Count (*) >=2
)