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>
【源於本人筆記】 若有書寫錯誤,表達錯誤,請指正...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28602568/viewspace-2076239/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Mybatis-Plus 更新欄位為 NULLMyBatisNull
- mybatis update並非所有欄位需要更新的解決辦法MyBatis
- 【Mongo】mongo更新欄位為另一欄位的值Go
- MySQL-去掉不為null的欄位MySqlNull
- 更新欄位時更新時間不自動更新(不更新 updated_at 欄位)
- json轉化保留null欄位JSONNull
- 為什麼資料庫欄位要使用NOT NULL?資料庫Null
- 【解決方案】Oracle插入/更新CLOB欄位報ORA-01704:字串文字太長Oracle字串
- Oracle-欄位的新增Oracle
- pydantic 欄位欄位校驗
- sql 統計多個欄位的和(如果欄位中含有 null 的處理)SQLNull
- [BUG反饋]模型增加欄位後field_sort欄位未更新模型
- 快速將下劃線欄位改為駝峰欄位
- DB2_更新SQL欄位DB2SQL
- es 更新指定欄位的方法
- ORACLE LOB大欄位維護Oracle
- oracle fga審計(欄位級)Oracle
- fastadmin 新增欄位記圖片欄位AST
- laravel-query-builder 對於欄位 值為 null的排序方式LaravelUINull排序
- 要慎用mysql的enum欄位的原因MySql
- 無法在SAP Fiori UI上新增擴充套件欄位?原因可能就在這裡UI套件
- oracle增加欄位帶預設值Oracle
- oracle 修改表欄位的長度Oracle
- 查詢oracle欄位預設值Oracle
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- SAP MIGO 報錯-在例程WERT_SIMULIEREN欄位NEUER_PREIS中欄位溢位-Go
- 資料庫欄位為0000-00-00的解決辦法資料庫
- [20201224]order by欄位順序與查詢條件為NULL.txtNull
- java查詢資料庫,int型欄位為null的情況Java資料庫Null
- [BUG反饋]模型管理 > 欄位管理看不見任何欄位。這表明顯有欄位、!模型
- MySQL 更新同一個表不同欄位MySql
- MySQL欄位究竟是否需要設定成not nullMySqlNull
- Oracle 修改欄位型別和長度Oracle型別
- Oracle-批量修改欄位裡面的值Oracle
- sql語句修改欄位型別和增加欄位SQL型別
- MySQL 更新一個表裡的欄位等於另一個表某欄位的值MySql
- NocoBase 一週更新彙總:支援在閱讀態欄位欄位上啟用連結
- 表單欄位
- 欄位排除功能