转载

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>

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

正文到此结束

所属分类：编程技术

本文标签： 数据 sql 标题 WMS Oracle 文章 ACE web http update Select 目录微软
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

Oracle update set字段=nvl(n,0)还报《ORA-01407:无法更新字段为NULL》原因分析和解决

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流