For convenience, the following simple model is created and some test data is constructed:
In BSS,
-- Customer data table
Create Table MERs
(
Customer_id number (8) Not null, -- customer ID
City_name varchar2 (10) Not null, -- City
Customer_type char (2) not null, -- customer type
...
)
Create unique index pk_customers on MERs (customer_id)
For some reason, the customer's city information is not accurate,
Customer ServiceIn the CRM subsystem of the departmentServiceObtain the location of 20% of some customers
City and other accurate information, so you extract this part of information to a temporary table:
Create Table tmp_cust_city
(
Customer_id number (8) Not null,
Citye_name varchar2 (10) Not null,
Customer_type char (2) not null
)
1) The simplest form
-- It is confirmed that all the mermer_id values in the MERs table are 'beijing' if they are less than 1000'
-- Less than 1000 of the customers are old customers in the city before the company moved to the whole country :)
Update MERs
Set city_name = 'beijing'
Where customer_id & lt; 1000
2) two tables (multiple tables) join the update -- only join in the where clause
-- The extracted data is VIP and new data is included. Therefore, the customer category is updated by the way.
Update MERs a -- use an alias
Set customer_type = '01' -- 01 is vip, 00 is normal
Where exists (select 1
From tmp_cust_city B
Where B. customer_id = a. customer_id
)
3) two tables (multiple tables) are associated with update. The modified value is calculated from the other table.
Update MERs a -- use an 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 has more than 2 values
Update MERs a -- use an 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
)
Note that in this statement,
= (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
)
It is two independent subqueries. According to the execution plan, two articles are scanned for table B/index;
If the where condition is discarded, the entire table is created for table A 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 provide "enough" value, because tmp_cust_city is only part of the customer's information,
Therefore, an error is returned (if the specified column -- city_name can be NULL, it is another question ):
01407,000 00, "cannot update (% s) to NULL"
// * Cause:
// * Action:
An alternative method can be:
Update MERs A -- use an alias
Set city_name = nvl (select B. city_name from tmp_cust_city B
Where B. customer_id = A. customer_id), A. 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 this is not in line with the business logic
4) 3) In some cases, because table B records only 20-30% of Table A records,
Considering the use of index in Table A, Using cursor may bring better performance than associating update:
Set serveroutput on
Declare
Cursor city_cur is
Select customer_id, city_name
From tmp_cust_city
Order by customer_id;
Begin
For my_cur in city_cur Loop
Update MERs
Set city_name = my_cur.city_name
Where customer_id = my_cur.customer_id;
/** You can submit one or multiple rows to avoid table lock **/
-- If Mod (city_cur % rowcount, 10000) = 0 then
-- Dbms_output.put_line ('----');
-- Commit;
-- End if;
End loop;
End;
5) a special case of associated update and further exploration of performance
In Oracle's update statement syntax, in addition to the update table, it can also be a view, so there is one special case:
Update (select a. city_name, B. city_name as new_name
From MERs,
Tmp_cust_city B
Where B. customer_id = A. customer_id
)
Set city_name = new_name
This avoids two scans on table B or its indexes, provided that a (customer_id) B (customer_id) must be a unique index
Or primary key. Otherwise, an error is returned:
01779,000 00, "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 Oracle Error
3) For some reason, tmp_cust_city mermer_id is not the unique index/primary key.
Update MERs a -- use an 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
)
When a given a. mermer_id
(Select B. city_name from tmp_cust_city B where B. customer_id = a. customer_id)
If one additional entry is returned, the following error is returned:
01427,000 00, "single-row subquery returns more than one row"
// * Cause:
// * Action:
A relatively simple approach that is similar to irresponsible is
Update MERs a -- use an 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 complicated multi-table join update statement, this error is often caused by weeks of consideration,
The preceding example is still described. A simple method is to import Table A into the value expression using group by and
Having statement to view Repeated Records
(Select B. customer_id, B. city_name, count (*)
From tmp_cust_city B, MERS MERs
Where B. customer_id = a. customer_id
Group by B. customer_id, B. city_name
Having count (*)> = 2
)
Author Blog: http://blog.csdn.net/aceplus/
Related Articles
Oracle update statement syntax and Performance Analysis
Microsoft mobile Windows
[Add to favorites] discussion on Anti-standard technology in Database Design
Oracle9i and SYBASE ASE12.5
How to install Windows 2000 on Windows XP
Comment on this article
Aceplus (2005-07-04)
/***** Correct 1 error ****/
A relatively simple approach that is similar to irresponsible is
Update MERs a -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B
Where B. customer_id =
A. customer_id and rownum = 1)
How to Understand the 01427 error. In a complicated multi-table join update statement, this error is often caused by weeks of consideration,
The preceding example is still described. A simple method is to import Table A into the value expression using group by and
Having statement to view Repeated Records
(Select B. customer_id, B. city_name, count (*)
From tmp_cust_city B, MERS MERs
Where B. customer_id = a. customer_id
Group by B. customer_id, B. city_name
Having count (*)> = 2
)
Aceplus (2005-07-04)
/***** Correct 1 error ****/
A relatively simple approach that is similar to irresponsible is
Update MERs a -- use an alias
Set city_name = (select B. city_name from tmp_cust_city B
Where B. customer_id =
A. customer_id rownum = 1)
How to Understand the 01427 error. In a complicated multi-table join update statement, this error is often caused by weeks of consideration,
The preceding example is still described. A simple method is to import Table A into the value expression using group by and
Having statement to view Repeated Records
(Select B. customer_id, B. city_name, count (*)
From tmp_cust_city B, MERS MERs
Where B. customer_id = A. customer_id
Group by B. customer_id, B. city_name
Having count (*)> = 2
)