Oracle Update statement syntax and performance analysis-multi-Table Association

For the sake of convenience, the following simple models are established and some test data are constructed:
In a business admissibility subsystem BSS,

SQL Code

-- Customer profile CREATE TABLE Customers (customer_id number (8null,- customer City_name VARCHAR2 (  tennull,--char(2null,-- customer type ...) Create unique index pk_customers on customers (customer_id)

For some reason, the information in the customer's city is not accurate, but
Customer service in the CRM subsystem, through proactive services to obtain a portion of the customer 20% is located
City and so on, you extract that part of the information into a temporary table:

SQL Code

CREATE TABLE Tmp_cust_city (customer_id number (8null, citye_name varchar2 (10  nullchar(2null)

1) The simplest form

SQL Code

--confirmed that all customer_id less than 1000 in the Customers table are ' Beijing '--within 1000 of the company's old customers in the city before the country:) update Customersset city_name=' Beijing 'where customer_id<

2) Two tables (multiple tables) associated update--only in the WHERE clause of the connection

SQL Code

--- use alias set customer_type= ' "--  vip,00 for normal  where exists (select1 from tmp_cust_city bwhere b.customer_id=a.customer_id)

3) Two tables (multiple tables) associated update--the modified value is calculated by another table

SQL Code

1Update customers A--using aliases2 SetCity_name= (SelectB.city_name fromTmp_cust_city bwhereB.customer_id=a.customer_id)3 whereExists (Select 14  fromtmp_cust_city b5 whereB.customer_id=a.customer_id6 )7--update more than 2 values8Update customers A--using aliases9 Set(City_name,customer_type) = (SelectB.city_name,b.customer_typeTen  fromtmp_cust_city b One whereB.customer_id=a.customer_id) A whereExists (Select 1 -  fromtmp_cust_city b - whereB.customer_id=a.customer_id the)

Note In this statement,

= (selectfromwhere

And

(Select1fromwhere

is two independent subqueries, to view the execution plan, the B/Index scan 2;
If you discard the where condition, the default is to make a table full table
Updated, but because

SQL Code

Select  from where b.customer_id=a.customer_id

It may not be possible to provide a "sufficient" value because tmp_cust_city is only part of the customer's information, so an error (if the specified column--city_name can be null is another matter):

SQL Code

01407 00000 " cannot update (%s) to NULL " // *cause: // *action:

An alternative approach can take:

SQL Code

Update customers A-- using the alias set city_name=nvl (selectfromwhere b.customer_id=a.customer_id), A.city_name)

Or

SQL Code

Set CITY_NAME=NVL ((selectfromwhere b.customer_id=a.customer_id),' Unknown ')

--Of course it doesn't fit the business logic.

4) the above 3) in some cases, since the record of Table B is only a record number of 20-30% of a,
Considering the use of index in a table, using the cursor may result in better performance than the associated update:

SQL Code

1. Setserveroutput ondeclarecursor city_cur isSelectCustomer_id,city_name fromTmp_cust_cityorder by Customer_id;begin forMy_curinchcity_cur Loopupdate CustomersSetCity_name=My_cur.city_namewhereCustomer_id=my_cur.customer_id;/** This can also be submitted in single/partial batches to avoid lock-in table conditions **/--ifMoD (City_cur%rowcount,10000)=0 Then--Dbms_output.put_line ('----');--commit;--Endif; end Loop;end;

5) A special case of associated update and its performance re-discussion
In Oracle's UPDATE statement syntax, in addition to the Update table, you can also be a view, so there are 1 exceptions:

SQL Code

Update (Select as new_name from customers a,tmp_cust_city bwhere b.customer_id=a.customer_id)set city_name=new_name

This avoids 2 scans of table B or its index, but only if a (customer_id) b (customer_id) must be a unique index or primary key. otherwise error:

SQL Code

01779 00000 " cannot modify a column which maps to a non key-preserved table " // *cause:an attempt was made to insert or update columns of a join view which // map to a non-key-preserved table. // *action:modify the underlying base tables directly.

6) Another common error with Oracle
Back to 3) situation, for some reason, tmp_cust_city customer_id is not the only index/primary key

SQL Code

Update customers A-- using alias set city_name= (selectfromwhere b.customer_id= a.customer_id) where exists (select1 from tmp_cust_city bwhere 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)
Returns the case of excess 1, the following error is reported:

SQL Code

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

A comparatively simple approximation to irresponsible practice is

SQL Code

Update customers A-- using the alias set city_name= (selectfromwhere b.customer_id=a.customer_id and rownum=1)

How to understand the 01427 error, in a very complex multi-table connection UPDATE statement, often due to ill-conceived, this error occurred,
As described in the example above, a simpler approach is to use the A-table in the value expression, using GROUP by and
Having words to view duplicate records

SQL Code

(Select b.customer_id,b.city_name,count (*) from tmp_cust_city b,customers a  where b.customer_id=a.customer_idgroup by b.customer_id,b.city_namehaving count (*) >=2 )

Oracle Update statement syntax and performance analysis-multi-Table Association