Application of the update statement in Oracle multi-Table Association

The following article describes the actual application of the update statement in Oracle multi-Table Association, we have established the following simple model and constructed part of the test data: in a business acceptance subsystem BSS,

Customer Data Table

 
 

  
  
create table customers  
  
  
(  
  
  
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 customers (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:

 
 

  
  
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 mermer_id less than 1000 in the MERs table are 'beijing'

Less than 1000 of the customers are old customers in the city before the company moved to the country :)

 
 

  
  
update customers 
 
 

Set city_name = 'beijing'

 
 

  
  
where customer_id<1000 
 
 

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

The extracted data is VIP and includes new ones. Therefore, the customer category is updated by the way.

 
 

  
  
update customers a  
 
 

Use 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) The modified values of Oracle two-table (Multi-table) associated update are calculated from the other table.

 
 

  
  
update customers a  
 
 

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

The above content is the description of the update statement associated with Oracle multi-table, hoping to help you in this regard.