Oracle Multi-Table Association UPDATE statement

Oracle Multi-Table Association UPDATE statement 2013-12-23 17:15:49

Category: Oracle

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

2. --Customer Data sheet
4. Create Table Customers
6. (
8. CUSTOMER_ID Number (8) not null, --Customer indication
10. City_name Varchar2 (Ten) not null, --City
12. Customer_type char (2) not null, --Customer type
14. ...
16. )
18. 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

1. Create table Tmp_cust_city
2. (
3. CUSTOMER_ID Number (8) is not null,
4. Citye_name Varchar2 (Ten) not null,
5. Customer_type char (2) not null
6. )

1) The simplest form

SQL Code

1. --confirmed Customers table all customer_id less than 1000 are ' Beijing '
3. where customer_id<1000

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

SQL Code

1. --the extracted data are VIP and include new, so update the customer category
2. --using aliases
3. set customer_type= " --01" for 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) associated Update- -the modified value is calculated by another table

SQL Code

2. Update customers a --using aliases
4. Set City_name= (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id)
6. where exists (select 1
8. From Tmp_cust_city b
10. where b.customer_id=a.customer_id
12. )
14. --Update more than 2 values
16. Update customers a --using aliases
18. Set (City_name,customer_type) = (select B.city_name,b.customer_type
20. From Tmp_cust_city b
22. where b.customer_id=a.customer_id)
24. where exists (select 1
26. From Tmp_cust_city b
28. where b.customer_id=a.customer_id
30. )

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

1. Select B.city_name from tmp_cust_city b 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

1. 01407, 00000, "Cannot update (%s) to NULL"
2. *cause:
3. *Action:

An alternative approach can take:

SQL Code

1. Update customers a --using aliases
2. Set CITY_NAME=NVL ((select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id), A.city_name )

Or

SQL Code

1. SET&NBSP;CITY_NAME=NVL ((select b.city_name  from tmp_cust_city b where 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 20-30% of the number ofrecords,
Considering the use of index in a table, using the cursor may result in better performance than the associated update:

SQL Code

2. Set Serveroutput on
6. Declare
8. Cursor City_cur is
10. Select Customer_id,city_name
12. From tmp_cust_city
14. Order by customer_id;
16. Begin
18. For my_cur in City_cur loop
22. Update Customers
24. Set City_name=my_cur.city_name
26. where customer_id=my_cur.customer_id;
30. /** here can also be submitted in single/batch, avoid the lock table situation **/
32. --If mod (city_cur%rowcount,10000) =0 Then
34. --Dbms_output.put_line ('----');
36. --commit;
38. --End If;
40. End Loop;
42. 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

1. Update (Select A.city_name,b.city_name as new_name
2. From customers A,
3. Tmp_cust_city b
4. where b.customer_id=a.customer_id
5. )
6. 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

1. 01779, 00000,  "cannot modify a column which maps to a non key-preserved table "
2. //*cause:an attempt was made to insert  or update columns of a  join view which
3. //map to a non- Key-preserved table.
4. //*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

1. update customers a --using aliases
2. set city_name= (select b.city_name from 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 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

1. 01427, 00000, "Single-row subquery returns more than one row"
2. *cause:
3. *Action:

A comparatively simple approximation to irresponsible practice is

SQL Code

1. update customers a --using aliases
2. 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 01427 errors, in a very complex multi-table connection Update statement, often due to ill-conceived, this error occurs,
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

1. (Select B.customer_id,b.city_name,count (*)
2. From Tmp_cust_city b,customers A
3. where b.customer_id=a.customer_id
4. GROUP BY B.customer_id,b.city_name
5. Having count (*) >=2
6. )

------------------------------------------------------------

http://blog.itpub.net/29378313/viewspace-1064069/

Oracle Multi-Table Association UPDATE statement