ORACLE multi-table join UPDATE statement

It. oyksoft. for convenience, compost641 has created the following simple model and constructed some test data: in a business acceptance subsystem BSS, the SQL code-the customer data table createtablecustomers (mermer_idnumber (8) notnull, -- the customer identifies city_namevarchar2 (10) notnull,

Http://it.oyksoft.com/post/641/ for convenience, the following simple model is established and part of the test data is constructed: In a business acceptance subsystem BSS, the SQL code -- customer data table create table customers (customer_id number (8) not null, -- customer ID city_name varchar2 (10) not null,

Http://it.oyksoft.com/post/641/

For convenience, the following simple model is created and some test data is constructed:
In BSS,

SQL code

1. -- Customer data table
2. Createtable MERs
3. (
4. Customer_id number (8) notnull, -- customer ID
5. City_name varchar2 (10) notnull, -- City
6. Customer_type char (2) notnull, -- customer type
7. ...
8. )
9. Createuniqueindex PK_customers on MERs (customer_id)

For some reason, the customer's city information is not accurate,
In the CRM subsystem of the customer service department, 20% of the customers are obtained through the active service
City and other accurate information, so you extract this part of information to a temporary table:

SQL code

1. Createtable tmp_cust_city
2. (
3. Customer_id number (8) notnull,
4. Citye_name varchar2 (10) notnull,
5. Customer_type char (2) notnull
6. )

1) The simplest form

SQL code

1. -- It is confirmed that all the mermer_id values in the MERs table are 'beijing' if they are less than 1000'
2. -- Less than 1000 of the customers are old customers in the city before the company moved to the whole country :)
3. Update MERs
4. Set city_name = 'beijing'
5. Where customer_id & lt; 1000

2) two tables (multiple tables) join the update -- only join in the where clause

SQL code

1. -- The extracted data is VIP and new data is included. Therefore, the customer category is updated by the way.
2. Update MERs a -- use an alias
3. Set customer_type = '01' -- 01 is vip, 00 is normal
4. Where exists (select 1
5. From tmp_cust_city B
6. Where B. customer_id = a. customer_id
7. )

3) two tables (multiple tables) are associated with update. The modified value is calculated from the other table.

SQL code

1. Update MERs a -- use an alias
2. Set city_name = (select B. city_namefrom tmp_cust_city B where B. customer_id = a. customer_id)
3. Where exists (select 1
4. From tmp_cust_city B
5. Where B. customer_id = a. customer_id
6. )
7. -- Update has more than 2 values
8. Update MERs a -- use an alias
9. Set (city_name, customer_type) = (select B. city_name, B. customer_type
10. From tmp_cust_city B
11. Where B. customer_id = a. customer_id)
12. Where exists (select 1
13. From tmp_cust_city B
14. Where B. customer_id = a. customer_id
15. )

Note that in this statement,
= (Select B. city_name, B. customer_typefrom 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

SQL code

1. Select B. city_name from tmp_cust_city B 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, so an error is returned (if the specified column -- city_name can be NULL, it is another question ):

SQL code

1. 01407,000 00, "cannot update (% s) to NULL"
2. // * Cause:
3. // * Action:

An alternative method can be:

SQL code

1. Update MERs a -- use an alias
2. Set city_name = nvl (select B. city_namefrom tmp_cust_city B where B. customer_id = a. customer_id), a. city_name)

Or

SQL code

1. Set city_name = nvl (select B. city_namefrom 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:

SQL code

1. Set serveroutput on
2. 
   
3. Declare
4. Cursor city_cur is
5. Select customer_id, city_name
6. From tmp_cust_city
7. Orderby customer_id;
8. Begin
9. For my_cur in city_cur loop
10. 
    
11. Update MERs
12. Set city_name = my_cur.city_name
13. Where customer_id = my_cur.customer_id;
14. 
    
15. /** You can submit one or multiple rows to avoid table lock **/
16. -- If mod (city_cur % rowcount, 10000) = 0 then
17. -- Dbms_output.put_line ('----');
18. -- Commit;
19. -- End if;
20. End loop;
21. 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:

SQL code

1. Update (select a. city_name, B. city_nameas new_name
2. From MERs,
3. Tmp_cust_city B
4. Where B. customer_id = a. customer_id
5. )
6. 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:

SQL code

1. 01779,000 00, "cannot modify a column which maps to a non key-preserved table"
2. // * Cause: An attempt was made toinsertorupdate columns of ajoinview which
3. // Map to a non-key-preservedtable.
4. // * 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.

SQL code

1. Update MERs a -- use an alias
2. Set city_name = (select B. city_namefrom tmp_cust_city B where B. customer_id = a. customer_id)
3. Where exists (select 1
4. From tmp_cust_city B
5. Where B. customer_id = a. customer_id
6. )

When a given a. mermer_id
(Select B. city_namefrom tmp_cust_city bwhere B. customer_id = a. customer_id)
If one additional entry is returned, the following error is returned:

SQL code

1. 01427,000 00, "single-row subquery returns more than one row"
2. // * Cause:
3. // * Action:

A relatively simple approach that is similar to irresponsible is

SQL code

1. Update MERs a -- use an alias
2. Set city_name = (select B. city_namefrom tmp_cust_city B where B. customer_id = a. customer_idand 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

SQL code

1. (Select B. customer_id, B. city_name, count (*)
2. From tmp_cust_city B, MERS MERs
3. Where B. customer_id = a. customer_id
4. Groupby B. customer_id, B. city_name
5. Havingcount (*)> = 2
6. )