ORACLE多表關聯UPDATE語句

　　為了方便起見建立了以下簡單模型和構造了部分測試數據:

　　在某個業務受理子系統BSS中

　　客戶資料表

　　create table customers

　　(

　　customer_id   number()    not null  客戶標示

　　city_name     varchar() not null  所在城市

　　customer_type char()      not null  客戶類型

　　

　　)

　　create unique index PK_customers on customers (customer_id)

　　由於某些原因客戶所在城市這個信息並不什麼准確但是在

　　客戶服務部的CRM子系統中通過主動服務獲取了部分客戶%的所在

　　城市等准確信息於是你將該部分信息提取至一張臨時表中

　　create table tmp_cust_city

　　(

　　customer_id     number() not null

　　citye_name     varchar() not null

　　customer_type  char()   not null

　　)

　　) 最簡單的形式

　　經確認customers表中所有customer_id小於均為北京

　　以內的均是公司走向全國之前的本城市的老客戶:)

　　update customers

　　set    city_name=北京

　　where  customer_id<

　　) 兩表(多表)關聯update 僅在where字句中的連接

　　這次提取的數據都是VIP且包括新增的所以順便更新客戶類別

　　update customers   a       使用別名

　　set    customer_type= 為vip為普通

　　where  exists (select

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　) 兩表(多表)關聯update 被修改值由另一個表運算而來

　　update customers a   使用別名

　　set    city_name=(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)

　　where  exists (select

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　 update 超過個值

　　update customers a   使用別名

　　set    (city_namecustomer_type)=(select bcity_namebcustomer_type

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id)

　　where  exists (select

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　注意在這個語句中

　　=(select bcity_namebcustomer_type

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　與

　　(select

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　是兩個獨立的子查詢查看執行計劃可知對b表/索引掃描了篇

　　如果捨棄where條件則默認對A表進行全表

　　更新但由於(select bcity_name from tmp_cust_city b where where  bcustomer_id=acustomer_id)

　　有可能不能提供足夠多值因為tmp_cust_city只是一部分客戶的信息

　　所以報錯（如果指定的列city_name可以為NULL則另當別論）

　　 cannot update (%s) to NULL

　　// *Cause:

　　// *Action:

　　一個替代的方法可以采用

　　update customers a   使用別名

　　set    city_name=nvl((select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)acity_name)

　　或者

　　set    city_name=nvl((select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)未知)

　　 當然這不符合業務邏輯了

　　) 上述)在一些情況下因為B表的紀錄只有A表的%的紀錄數

　　考慮A表使用INDEX的情況使用cursor也許會比關聯update帶來更好的性能

　　set serveroutput on

　　declare

　　cursor city_cur is

　　select customer_idcity_name

　　from   tmp_cust_city

　　order by customer_id;

　　begin

　　for my_cur in city_cur loop

　　update customers

　　set    city_name=my_curcity_name

　　where  customer_id=my_curcustomer_id;

　　/** 此處也可以單條/分批次提交避免鎖表情況 **/

　　     if mod(city_cur%rowcount)= then

　　         dbms_outputput_line();

　　         commit;

　　     end if;

　　end loop;

　　end;

　　) 關聯update的一個特例以及性能再探討

　　在oracle的update語句語法中除了可以update表之外也可以是視圖所以有以下個特例

　　update (select acity_namebcity_name as new_name

　　from   customers a

　　tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　set    city_name=new_name

　　這樣能避免對B表或其索引的次掃描但前提是 A(customer_id) b(customer_id)必需是unique index

　　或primary key否則報錯

　　 cannot modify a column which maps to a non keypreserved table

　　// *Cause: An attempt was made to insert or update columns of a join view which

　　//         map to a nonkeypreserved table

　　// *Action: Modify the underlying base tables directly

　　）oracle另一個常見錯誤

　　回到)情況由於某些原因tmp_cust_city customer_id 不是唯一index/primary key

　　update customers a   使用別名

　　set    city_name=(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)

　　where  exists (select

　　from   tmp_cust_city b

　　where  bcustomer_id=acustomer_id

　　)

　　當對於一個給定的acustomer_id

　　(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id)

　　返回多余條的情況則會報如下錯誤

　　 singlerow subquery returns more than one row

　　// *Cause:

　　// *Action:

　　一個比較簡單近似於不負責任的做法是

　　update customers a   使用別名

　　set    city_name=(select bcity_name from tmp_cust_city b where bcustomer_id=acustomer_id and rownum=)

　　如何理解 錯誤在一個很復雜的多表連接update的語句經常因考慮不周出現這個錯誤

　　仍已上述例子來描述一個比較簡便的方法就是將A表代入 值表達式 中使用group by 和

　　having 字句查看重復的紀錄

　　(select bcustomer_idbcity_namecount(*)

　　from tmp_cust_city bcustomers a

　　where bcustomer_id=acustomer_id

　　group by bcustomer_idbcity_name

　　having count(*)>=

　　)

From:http://tw.wingwit.com/Article/program/Oracle/201311/18909.html