ORACLE 關連更新 update select，oracleupdate
總結: 關鍵的地方是where 語句的加入. 在11G中, 如果不加11G , 或造成除匹配的行數更新為相應的值之後, 其余的會變成負數.
所以, 測試的辦法就是: 先查看需要更新的數量即連接的數量究竟有多少行, 然後update 之後多少行, 才能確定條件是否正確.
引入知識比較全面的地址:
http://www.blogjava.net/Jhonney/archive/2010/06/25/324503.html. 文章最後注明轉載.
本人用到的2處SQL:
1 :
UPDATE SFISM4.R_DT_DIP_TRACEABILITY_T B SET (B.CURRENT_QTY, B.UPDATE_TIME, B.UPDATE_USER)=(SELECT DECODE(SIGN(B.CURRENT_QTY - A.KP_COUNT),-1,0, B.CURRENT_QTY - A.KP_COUNT), SYSDATE,:VUSER FROM SFIS1.C_DT_DIP_BOM_T A, SFISM4.R_MO_BASE_T C WHERE A.BOM_NO = B.BOM_NO AND B.BOM_NO = C.KEY_PART_NO AND A.KEY_PART_NO = B.KEY_PART_NO AND C.MO_NUMBER =:VBOM AND B.TRAC_LOT =:VTRACELOT)WHERE B.TRAC_LOT =:VTRACELOT AND EXISTS (SELECT 1 FROM SFIS1.C_DT_DIP_BOM_T AA WHERE B.BOM_NO = AA.BOM_NO AND B.KEY_PART_NO = AA.KEY_PART_NO) UPDATE SFISM4.R_REEL_TRACKING_T A SET A.REMAIN_QTY =(SELECT B.REEL_QTY FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B WHERE A.REEL_ID = B.REEL_ID AND A.REMAIN_QTY <0 AND A.REMAIN_QTY <> B.REEL_QTY) WHERE EXISTS (SELECT 1 FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B WHERE A.REEL_ID = B.REEL_ID AND A.REMAIN_QTY <0 AND A.REMAIN_QTY <> B.REEL_QTY);
2:
UPDATE SFISM4.R_REEL_TRACKING_T A SET A.REMAIN_QTY =(SELECT B.REEL_QTY FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B WHERE A.REEL_ID = B.REEL_ID AND A.REMAIN_QTY <0 AND A.REMAIN_QTY <> B.REEL_QTY) WHERE EXISTS (SELECT 1 FROM SFISM4.R_MMS_STOCK_SMT_SA_DETAIL_T B WHERE A.REEL_ID = B.REEL_ID AND A.REMAIN_QTY <0 AND A.REMAIN_QTY <> B.REEL_QTY);
最後是引用比較好的說明如下, 進行標注: 原文網址: http://www.blogjava.net/Jhonney/archive/2010/06/25/324503.html
SQL> select * from wwm2; --要更新的表 , 多一點.
TOWN ID
-------------------- ----------
222 222
111 111
ww'jj 111
llll 1111
dddd 2222
lllldf 111
lllldf 111
dsafdf 111
3435 111
ljjjjj 222
dsafdf 111
TOWN ID
-------------------- ----------
3435 111
ljjjjj 222
SQL> select * from wwm5; --更新的條件表 , 少一點
TOWN ID
-------------------- ----------
lllldf 111
test 9984
SQL> select wwm2.* from wwm2,wwm5 where wwm2.id=wwm5.id
2 /
TOWN ID
-------------------- ----------
111 111
ww'jj 111
lllldf 111
lllldf 111
dsafdf 111
3435 111
dsafdf 111
3435 111
8 rows selected.
所以,每次需要更新8條數據就是正確的. 這一步是驗證更新是不是對, 錯的關鍵.!!!
相信程序員是通過以下類似的SQL更新的,這是錯誤的,因為沒有加WHERE
SQL> update wwm2 set wwm2.town=(select wwm5.town from wwm5 where wwm5.id=wwm2.id)
2 /
13 rows updated.
SQL> select * from wwm2;
TOWN ID
-------------------- ----------
222
lllldf 111
lllldf 111
1111
2222
lllldf 111
lllldf 111
lllldf 111
lllldf 111
222
lllldf 111
TOWN ID
-------------------- ----------
lllldf 111
222
13 rows selected.
可以看到13條記錄被更新,符合條件的更新正確,不符合條件的也更新為NULL.以下是正確的方法
方法一:
SQL> update wwm2
2 set town=(select town from wwm5 where wwm5.id=wwm2.id)
3 where id=(select wwm5.id from wwm5 where wwm5.id=wwm2.id)
4 /
8 rows updated.
方法二: 與方法一道理相同,這裡需要掌握EXIST的相關用法.
SQL> update wwm2
set town=(select town from wwm5 where wwm5.id=wwm2.id)
where exists (select 1 from wwm5 where wwm5.id=wwm2.id)
8 rows updated.
方法三:
SQL> update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id)
2 set atown=btown
3 /
set atown=btown
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
1* alter table wwm5 add primary key (id)
SQL> /
Table altered.
1 update (select a.town atown,a.id aid,b.town btown,b.id bid from wwm2 a,wwm5 b where a.id=b.id) -- 表連接之後, 更新視圖的方式很好理解. 但是需要primary key.
2* set atown=btown
SQL> /
8 rows updated.
這種方法的局限性就是需要PRIMARY 的支持.! 需要 更新被更新表有主鍵, 連接欄位是不是需要呢>?
方法四: --萬金油. 但是數據庫顧問說效能沒有使用表連接好.
1 declare
2 cursor cur_wwm is select town,id from wwm5; -- 驅動 表
3 begin
4 for my_wwm in cur_wwm loop
5 update wwm2 set town=my_wwm.town -- 被驅動表.
6 where id=my_wwm.id;
7 end loop;
8* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from wwm2;
TOWN ID
-------------------- ----------
222 222
lllldf 111
lllldf 111
llll 1111
dddd 2222
lllldf 111
lllldf 111
lllldf 111
lllldf 111
ljjjjj 222
lllldf 111
TOWN ID
-------------------- ----------
lllldf 111
ljjjjj 222
這個方法是最靈活的了.
方法五:
注意,方法五只能適用於WWM5是WWM2的子集的時候. (新特性 merge Into,哎呀! 不想學.....)
1 merge into wwm2
2 using (select town,id from wwm5) b
3 on (wwm2.id=b.id)
4 when matched then update set town=b.town
5* when not matched then insert (town,id) values (null,null)
SQL> /
9 rows merged.
SQL> select * from wwm2;
TOWN ID
-------------------- ----------
---注意這個地方,被插入了一個空值.因為WWM5的ID=9984在WWM2中不能匹配,根本原因是ORACLE9必須有WHEN NOT MATCHED子句,但是ORACLE10可以不許要,也就是ORACLE10可以不寫WHEN NOT MATCHED ,就不必插入NULL值了,為解決這個問題,下一步會DELETE WWM5的ID=9984,這樣一來就不會執行WHEN NOT MATCHED
222 222
lllldf 111
lllldf 111
llll 1111
dddd 2222
lllldf 111
lllldf 111
lllldf 111
lllldf 111
ljjjjj 222
TOWN ID
-------------------- ----------
lllldf 111
lllldf 111
ljjjjj 222
14 rows selected.
SQL> delete from wwm5 where id=9984;
1 row deleted.
SQL> 1 merge into wwm2
SQL> 2 using (select town,id from wwm5) b
SQL> 3 on (wwm2.id=b.id)
SQL> 4 when matched then update set town=b.town
SQL> 5* when not matched then insert (town,id) values (null,null)
SQL> /
8 rows merged.
以上就是5種關連更新的例子了,希望能給開發人員解惑.
說明：如果select 子句可以返回多行記錄，但返回適合where條件的記錄只能是唯一的，否則將會報返回單行的select子句返回多行的錯誤，因為update只能跟據此處的where子句（內層where)進行相應記錄的匹配更新，一次只能是一條。
來自為知筆記(Wiz)
