Oracle update set字段=nvl(n,0)还报《ORA-01407:无法更新字段为NULL》原因分析和解决
链接：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>
【源于本人笔记】 若有书写错误，表达错误，请指正...
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
