mysql multi-table association update

This article shares the method of implementing multi-table association update in mysql, and shares some commonly used examples of multi-table update for your reference.

Recommended courses: MySQL Tutorial.

Create the following simple model and construct some test data:
In a certain business acceptance subsystem BSS,

--客户资料表
create table customers
(
customer_id number(8) not null, -- 客户标示
city_name varchar2(10) not null, -- 所在城市
customer_type char(2) not null, -- 客户类型
...
)
create unique index PK_customers on customers (customer_id)

due to some The reason is that the information about the city where the customer is located is not accurate, but in the CRM subsystem of the customer service department, accurate information such as the city where 20% of some customers are located is obtained through proactive services, so you extract this part of the information to a temporary In the 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

--经确认customers表中所有customer_id小于1000均为'北京'
--1000以内的均是公司走向全国之前的本城市的老客户:)
update customers
set city_name='北京'
where customer_id<1000

2) Two tables (multiple tables) related update -- only the connection in the where clause

--这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别
update customers a -- 使用别名
set customer_type='01' --01 为vip，00为普通
where exists (select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)

3) Two-table (multiple-table) association update -- the modified value is calculated from another table

update customers a -- 使用别名
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 超过2个值
update customers a -- 使用别名
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
)
与
(select 1
from tmp_cust_city b
where b.customer_id=a.customer_id
)

is two independent sub- Query, check the execution plan and you can see that 2 articles have been scanned for table b/index;

If the where condition is discarded, table A will be updated in the entire table by default

, but due to (select b. city_name from tmp_cust_city b where where b.customer_id=a.customer_id)

It is possible that "enough" values ​​cannot be provided, because tmp_cust_city is only part of the customer's information,

so an error is reported (if specified Column --city_name can be NULL, which is another matter):

01407, 00000, "cannot update (%s) to NULL"

// *Cause:

// *Action:

Replacement method:

update customers a -- 使用别名
set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)
或者
set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),'未知')
-- 当然这不符合业务逻辑了

A relatively simple method is to substitute table A into the value expression and use group by and having words to view duplicate records.

(select b.customer_id,b.city_name,count(*)
 from tmp_cust_city b,customers a
 where b.customer_id=a.customer_id
 group by b.customer_id,b.city_name
 having count(*)>=2
)

The above is the detailed content of mysql multi-table association update. For more information, please follow other related articles on the PHP Chinese website!