ORACLE多表關聯(lián)UPDATE語句

1、ORACLE 多表關聯(lián) UPDATE 語句為了方便起見 ,建立了以下簡單模型 , 和構造了部分測試數(shù)據(jù) 在某個業(yè)務受理子系統(tǒng)BSS中，- 客戶資料表create tablecustomerscustomer_idnumber(8)not null ,- 客戶標示city_namevarchar2(10)not null ,- 所在城市customer_type char(2)not null ,- 客戶類型)create unique index PK_customers on customers (customer_id) 由于某些原因，客戶所在城市這個信息并不什么準確，但是在客戶服務部的C

2、RM子系統(tǒng)中，通過主動服務獲取了部分客戶20%勺所在城市等準確信息，于是你將該部分信息提取至一張臨時表中： create table tmp_cust_city(customer_id number(8) not null , citye_name varchar2(10) not null ， customer_type char (2) not null)1) 最簡單的形式- 經確認 customers 表中所有 customer_id 小于 1000 均為 ' 北京 ' -1000 以內的均是公司走向全國之前的本城市的老客戶 :)update customers setc

3、ity_name= ' 北京'where customer_id<10002) 兩表(多表)關聯(lián)update -僅在where字句中的連接-這次提取的數(shù)據(jù)都是VIP,且包括新增的，所以順便更新客戶類別 update customers a- 使用別名set customer_type= '01'-01 為 vip ， 00 為普通where exists ( select 1from tmp_cust_city b where b.customer_id=a.customer_id)3) 兩表 ( 多表) 關聯(lián) update - 被修改值由另一個表運算而來

4、update customers a - 使用別名whereset city_name=( select b.city_name from tmp_cust_city b b.customer_id=a.customer_id)where exists ( select 1from tmp_cust_city bwhere b.customer_id=a.customer_id)- update 超過 2 個值 update customers a - 使用別名set (city_name,customer_type)=( select b.city_name,b.customer_typef

5、rom tmp_cust_city bwhere b.customer_id=a.customer_ id)where exists ( select 1from tmp_cust_city bwhere b.customer_id=a.customer_id) 注意在這個語句中，=( select b.city_name,b.customer_typefrom tmp_cust_city bwhere b.customer_id=a.customer_id)與(select1fromtmp_cust_city bwhereb.customer_id=a.customer_id)是兩個獨立的子

6、查詢，查看執(zhí)行計劃可知，對b 表/索引掃描了 2 篇；如果舍棄where條件，則默認對A表進行全表更 新 ， 但 由 于 ( select b.city_name from tmp_cust_city b where where b.customer_id=a.customer_id)有可能不能提供 "足夠多 "值，因為 tmp_cust_city 只是一部分客戶的信息， 所以報錯(如果指定的列-city_name可以為NULL則另當別論)：01407, 00000, "cannot update (%s) to NULL"/ *Cause:/ * Ac

7、tion :一個替代的方法可以采用：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), ' 未知 ' )當然這不符合業(yè)務邏輯了4）上述3）在一些情況下，因為B表的紀錄只有A表的20

8、-30%的紀錄數(shù)， 考慮A表使用INDEX的情況，使用cursor也許會比關聯(lián)update帶來更好的 性能：set serveroutput ondeclare cursor select fromcity_cur is customer_id,city_name tmp_cust_cityorder by customer_id;beginfor my_cur in city_cur loopupdate customersset city_name=my_cur.city_namewhere customer_id=my_cur.customer_id;/* 此處也可以單條 / 分批次提交

9、，避免鎖表情況 */- if mod(city_cur%rowcount,10000)=0 then-dbms_output.put_line(' ');-commit;- end if;end loop;end;5) 關聯(lián) update 的一個特例以及性能再探討在 oracle 的 update 語句語法中，除了可以 update 表之外，也可以是視圖，所以有以下 1 個特例：update (select a.city_name,b.city_name as new_namefrom customers a, tmp_cust_city bwhere b.customer_i

10、d=a.customer_id)set city_name=new_name這樣能避免對 B 表或其索引的 2 次掃描，但前提是 A(customer_id)b(customer_id) 必需是 unique index或 primary key 。否則報錯：01779, 00000, "cannot modify a column which maps to a non key-preserved table"/ *Cause: An attempt was made to insert or update columns of a join viewwhich/ map

11、 to a non- key-preserved table./ * Action : Modify the underlying base tables directly.6) oracle 另一個常見錯誤回 到 3) 情 況 , 由 于 某 些 原 因 ， tmp_cust_city customer_id 不 是 唯 一 index/ primary keyupdate customers a - 使用別名set city_name=( select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)wh

12、ere exists ( select 1from tmp_cust_city b where b.customer_id=a.customer_id)當對于一個給定的 a.customer_id( select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id)返回多余 1 條的情況，則會報如下錯誤：01427, 00000, "single-row subquery returns more than one row"/ *Cause:/ * Action :一個比較簡單近似于不負責任的做法是update customers a - 使用別名set city_name=( select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id and rownum=1)如何理解 01427 錯誤，在一個很復雜的多表連接 update 的語句，經常因考 慮不周，出現(xiàn)這個錯誤，仍已上述例子來描述，一個比較簡便的方法就是將 A 表代入 值