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
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