.jpg)
本文分享下，在mysql中實(shí)現(xiàn)多表關(guān)聯(lián)更新update的方法，分享一些常用的update多表更新的例子，供大家學(xué)習(xí)參考。
推薦課程：MySQL教程。
創(chuàng)建以下的簡單模型，并構(gòu)造部分測試數(shù)據(jù):
在某個(gè)業(yè)務(wù)受理子系統(tǒng)BSS中,
--客戶資料表 create table customers ( customer_id number(8) not NULL, -- 客戶標(biāo)示 city_name varchar2(10) not null, -- 所在城市 customer_type char(2) not null, -- 客戶類型 ... ) create unique index PK_customers on customers (customer_id)
由于某些原因，客戶所在城市這個(gè)信息并不什么準(zhǔn)確，但是在客戶服務(wù)部的CRM子系統(tǒng)中，通過主動(dòng)服務(wù)獲取了部分客戶20%的所在城市等準(zhǔn)確信息，于是你將該部分信息提取至一張臨時(shí)表中：
create table tmp_cust_city ( customer_id number(8) not null, citye_name varchar2(10) not null， customer_type char(2) not null )
1) 最簡單的形式
--經(jīng)確認(rèn)customers表中所有customer_id小于1000均為'北京' --1000以內(nèi)的均是公司走向全國之前的本城市的老客戶:) update customers set city_name='北京' where customer_id<1000
2) 兩表(多表)關(guān)聯(lián)update — 僅在where字句中的連接
--這次提取的數(shù)據(jù)都是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) 兩表(多表)關(guān)聯(lián)update — 被修改值由另一個(gè)表運(yùn)算而來
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個(gè)值 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 )
注意在這個(gè)語句中，
(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 )
是兩個(gè)獨(dú)立的子查詢，查看執(zhí)行計(jì)劃可知，對(duì)b表/索引掃描了2篇；
如果舍棄where條件，則默認(rèn)對(duì)A表進(jìn)行全表
更新，但由于(select b.city_name from tmp_cust_city b where where b.customer_id=a.customer_id)
有可能不能提供”足夠多”值，因?yàn)閠mp_cust_city只是一部分客戶的信息，
所以報(bào)錯(cuò)（如果指定的列–city_name可以為NULL則另當(dāng)別論）：
01407, 00000, “cannot update (%s) to NULL”
// *Cause:
// *Action:
替換的方法：
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),'未知') -- 當(dāng)然這不符合業(yè)務(wù)邏輯了
一個(gè)比較簡便的方法就是將A表代入 值表達(dá)式 中,使用group by 和having 字句查看重復(fù)的紀(jì)錄。
(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 )
.png)
