連結：http://blog.itpub.net/28602568/viewspace-2076239/

標題：Oracle update set欄位=nvl(n,0)還報《ORA-01407:無法更新欄位為NULL》原因分析和解決

原SQL執行情況：

SQL> update usr_wms_city.con_content x set x.qty =(

select nvl(j.qty,0) from (select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality,max(a.createtm)createtm,sum(a.qty)qty from usr_wms_city.con_content a where (a.instock_qty=0 or a.outstock_qty=0)

group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1 )j

where x.locno=j.locno and x.cell_no=j.cell_no and x.item_no=j.item_no and x.size_no=j.size_no

and x.barcode=j.barcode

and x.item_type=j.item_type and x.quality=j.quality and x.createtm=j.createtm )

where exists( select * from ( select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality,min(a.createtm)createtm

from usr_wms_city.con_content a

where (a.instock_qty=0 or a.outstock_qty=0)

group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1 )g

where x.locno=g.locno and x.cell_no=g.cell_no and x.item_no=g.item_no and x.size_no=g.size_no

and x.barcode=g.barcode

and x.item_type=g.item_type and x.quality=g.quality

and x.createtm=g.createtm );

ORA-01407: 無法更新 ("USR_WMS_CITY"."CON_CONTENT"."QTY") 為 NULL

SQL>

原因：

是因為 where查詢的關聯條件【min(a.createtm)=x.createtm】後的資料有不存在在set【max(a.createtm)=x.createtm】的資料，這樣的資料會自動更新為null，所以即使set 欄位=nvl(j.qty,0) 也會出現報《ORA-01407: 無法更新 ("USR_WMS_CITY"."CON_CONTENT"."QTY") 為 NULL》錯誤的情況；

驗證有多少條不匹配的資料：

SQL> SELECT count(1) FROM usr_wms_city.con_content x where not exists(select 1 from

2 ( select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality,max(a.createtm) createtm from

3 usr_wms_city.con_content a

4 where (a.instock_qty=0 or a.outstock_qty=0)

5 group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1 )j

6 where x.locno=j.locno and x.cell_no=j.cell_no and x.item_no=j.item_no and x.size_no=j.size_no

7 and x.barcode=j.barcode

8 and x.item_type=j.item_type and x.quality=j.quality and x.createtm=j.createtm

9 ) and (locno,x.cell_no,x.size_no,x.item_no,x.barcode,x.item_type,x.quality,x.createtm) in (

10 select locno, cell_no, size_no, item_no, barcode, item_type, quality,createtm from (

11 select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality, min(a.createtm) createtm

12 from usr_wms_city.con_content a

13 where (a.instock_qty=0 or a.outstock_qty=0)

14 group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1 ) );

COUNT(1)

----------

293

解決方案：

SQL> --方案1 --改成merge 且join部分只寫一個通用條件（merge 匹配更新，不匹配再次不處理...）

SQL> MERGE INTO usr_wms_city.con_content x

2 USING

3 ( select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality,max(a.createtm)createtm,sum(nvl(a.qty,0))qty from

4 usr_wms_city.con_content a

5 where (a.instock_qty=0 or a.outstock_qty=0)

6 group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1

7 )

8 j ON (x.locno=j.locno and x.cell_no=j.cell_no and x.item_no=j.item_no and x.size_no=j.size_no

9 and x.barcode=j.barcode

10 and x.item_type=j.item_type and x.quality=j.quality and x.createtm=j.createtm )

11 WHEN MATCHED THEN UPDATE

12 set x.qty= nvl(j.qty,0);

293 rows merged

SQL>

SQL> --方案2 將set和where 的createtm要取相同的函式(都是max或者都是min)

SQL> update usr_wms_city.con_content x set x.qty =(

2 select nvl(j.qty,0) from (

3 select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality,max(a.createtm)createtm,sum(a.qty)qty from

4 usr_wms_city.con_content a

5 where (a.instock_qty=0 or a.outstock_qty=0)

6 group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1

7 )j

8 where x.locno=j.locno and x.cell_no=j.cell_no and x.item_no=j.item_no and x.size_no=j.size_no

9 and x.barcode=j.barcode

10 and x.item_type=j.item_type and x.quality=j.quality and x.createtm=j.createtm

11 )

12 where exists(

13 select * from (

14 select locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality,max(a.createtm)createtm

15 from usr_wms_city.con_content a

16 where (a.instock_qty=0 or a.outstock_qty=0)

17 group by locno,a.cell_no,a.size_no,a.item_no,a.barcode,a.item_type,a.quality having count(1)>1

18 )g

19 where x.locno=g.locno and x.cell_no=g.cell_no and x.item_no=g.item_no and x.size_no=g.size_no

20 and x.barcode=g.barcode

21 and x.item_type=g.item_type and x.quality=g.quality

22 and x.createtm=g.createtm

23 );

293 rows updated

SQL> rollback;

Rollback complete

SQL>

【源於本人筆記】若有書寫錯誤，表達錯誤，請指正...

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle update set欄位=nvl(n,0)還報《ORA-01407:無法更新欄位為NULL》原因分析和解決

相關文章