2023年8月3日发(作者：)
oracle条件为空⽆法进⾏更新,Oracleupdateset字段=nvl（n,0）还报。。。原SQL执⾏情况：SQL>update usr_wms__content x set =(selectnvl(,0)from (selectlocno,_no,_no,_no,e,_type,y,max(tm)createtm,sum()qtyfromusr_wms__content awhere (k_qty=0 or ck_qty=0)group by locno,_no,_no,_no,e,_type,y having count(1)>1)jwhere = and _no=_no and _no=_no and _no=_noand e=eand _type=_type and y=y and tm=tm)where exists(select * from (select locno,_no,_no,_no,e,_type,y,min(tm)createtmfrom usr_wms__content awhere (k_qty=0 or ck_qty=0)group by locno,_no,_no,_no,e,_type,y having count(1)>1)gwhere = and _no=_no and _no=_no and _no=_noand e=eand _type=_type and y=yand tm=tm);ORA-01407: ⽆法更新 ("USR_WMS_CITY"."CON_CONTENT"."QTY") 为 NULLSQL>原因：是因为 where查询的关联条件【min(tm)=tm】后的数据有不存在在set【max(tm)=tm】的数据，这样的数据会⾃动更新为null，所以即使set 字段=nvl(,0) 也会出现报《ORA-01407: ⽆法更新("USR_WMS_CITY"."CON_CONTENT"."QTY") 为 NULL》错误的情况；验证有多少条不匹配的数据：SQL> SELECT count(1) FROM usr_wms__content x where not exists(select 1 from2 ( select locno,_no,_no,_no,e,_type,y,max(tm)createtm from3 usr_wms__content a4 where (k_qty=0 or ck_qty=0)5 group by locno,_no,_no,_no,e,_type,y having count(1)>1 )j6 where = and _no=_no and _no=_no and _no=_no7 and e=e8 and _type=_type and y=y and tm=tm9 ) and (locno,_no,_no,_no,e,_type,y,tm) in (10 select locno, cell_no, size_no, item_no, barcode, item_type, quality,createtm from (11 select locno,_no,_no,_no,e,_type,y,min(tm)createtm12 from usr_wms__content a13 where (k_qty=0 or ck_qty=0)14 group by locno,_no,_no,_no,e,_type,y having count(1)>1 ) );COUNT(1)----------293解决⽅案：SQL>--⽅案1 --改成merge 且join部分只写⼀个通⽤条件 (merge 匹配更新，不匹配再次不处理...)SQL> MERGE INTO usr_wms__content x2 USING3 ( select locno,_no,_no,_no,e,_type,y,max(tm)createtm,sum(nvl(,0))qtyfrom4 usr_wms__content a5 where (k_qty=0 or ck_qty=0)6 group by locno,_no,_no,_no,e,_type,y having count(1)>17 )8 j ON (= and _no=_no and _no=_no and _no=_no9 and e=e10 and _type=_type and y=y and tm=tm )11 WHEN MATCHED THEN UPDATE12 =nvl(,0);293 rows mergedSQL>SQL>--⽅案2 将set和where 的createtm要取相同的函数(都是max或者都是min)SQL> update usr_wms__content x set =(2 select nvl(,0) from (3 select locno,_no,_no,_no,e,_type,y,max(tm)createtm,sum()qtyfrom4 usr_wms__content a5 where (k_qty=0 or ck_qty=0)6 group by locno,_no,_no,_no,e,_type,y having count(1)>17 )j8 where = and _no=_no and _no=_no and _no=_no9 and e=e10 and _type=_type and y=y and tm=tm11 )12 where exists(13 select * from (14 select locno,_no,_no,_no,e,_type,y,max(tm)createtm15 from usr_wms__content a16 where (k_qty=0 or ck_qty=0)17 group by locno,_no,_no,_no,e,_type,y having count(1)>118 )g19 where = and _no=_no and _no=_no and _no=_no20 and e=e21 and _type=_type and y=y22 and tm=tm23 );293 rows updatedSQL> rollback;Rollback completeSQL>【源于本⼈笔记】 若有书写错误，表达错误，请指正...此条⽬发表在Oracle分类⽬录。将固定连接加⼊收藏夹。
2023年8月3日发(作者：)
oracle条件为空⽆法进⾏更新,Oracleupdateset字段=nvl（n,0）还报。。。原SQL执⾏情况：SQL>update usr_wms__content x set =(selectnvl(,0)from (selectlocno,_no,_no,_no,e,_type,y,max(tm)createtm,sum()qtyfromusr_wms__content awhere (k_qty=0 or ck_qty=0)group by locno,_no,_no,_no,e,_type,y having count(1)>1)jwhere = and _no=_no and _no=_no and _no=_noand e=eand _type=_type and y=y and tm=tm)where exists(select * from (select locno,_no,_no,_no,e,_type,y,min(tm)createtmfrom usr_wms__content awhere (k_qty=0 or ck_qty=0)group by locno,_no,_no,_no,e,_type,y having count(1)>1)gwhere = and _no=_no and _no=_no and _no=_noand e=eand _type=_type and y=yand tm=tm);ORA-01407: ⽆法更新 ("USR_WMS_CITY"."CON_CONTENT"."QTY") 为 NULLSQL>原因：是因为 where查询的关联条件【min(tm)=tm】后的数据有不存在在set【max(tm)=tm】的数据，这样的数据会⾃动更新为null，所以即使set 字段=nvl(,0) 也会出现报《ORA-01407: ⽆法更新("USR_WMS_CITY"."CON_CONTENT"."QTY") 为 NULL》错误的情况；验证有多少条不匹配的数据：SQL> SELECT count(1) FROM usr_wms__content x where not exists(select 1 from2 ( select locno,_no,_no,_no,e,_type,y,max(tm)createtm from3 usr_wms__content a4 where (k_qty=0 or ck_qty=0)5 group by locno,_no,_no,_no,e,_type,y having count(1)>1 )j6 where = and _no=_no and _no=_no and _no=_no7 and e=e8 and _type=_type and y=y and tm=tm9 ) and (locno,_no,_no,_no,e,_type,y,tm) in (10 select locno, cell_no, size_no, item_no, barcode, item_type, quality,createtm from (11 select locno,_no,_no,_no,e,_type,y,min(tm)createtm12 from usr_wms__content a13 where (k_qty=0 or ck_qty=0)14 group by locno,_no,_no,_no,e,_type,y having count(1)>1 ) );COUNT(1)----------293解决⽅案：SQL>--⽅案1 --改成merge 且join部分只写⼀个通⽤条件 (merge 匹配更新，不匹配再次不处理...)SQL> MERGE INTO usr_wms__content x2 USING3 ( select locno,_no,_no,_no,e,_type,y,max(tm)createtm,sum(nvl(,0))qtyfrom4 usr_wms__content a5 where (k_qty=0 or ck_qty=0)6 group by locno,_no,_no,_no,e,_type,y having count(1)>17 )8 j ON (= and _no=_no and _no=_no and _no=_no9 and e=e10 and _type=_type and y=y and tm=tm )11 WHEN MATCHED THEN UPDATE12 =nvl(,0);293 rows mergedSQL>SQL>--⽅案2 将set和where 的createtm要取相同的函数(都是max或者都是min)SQL> update usr_wms__content x set =(2 select nvl(,0) from (3 select locno,_no,_no,_no,e,_type,y,max(tm)createtm,sum()qtyfrom4 usr_wms__content a5 where (k_qty=0 or ck_qty=0)6 group by locno,_no,_no,_no,e,_type,y having count(1)>17 )j8 where = and _no=_no and _no=_no and _no=_no9 and e=e10 and _type=_type and y=y and tm=tm11 )12 where exists(13 select * from (14 select locno,_no,_no,_no,e,_type,y,max(tm)createtm15 from usr_wms__content a16 where (k_qty=0 or ck_qty=0)17 group by locno,_no,_no,_no,e,_type,y having count(1)>118 )g19 where = and _no=_no and _no=_no and _no=_no20 and e=e21 and _type=_type and y=y22 and tm=tm23 );293 rows updatedSQL> rollback;Rollback completeSQL>【源于本⼈笔记】 若有书写错误，表达错误，请指正...此条⽬发表在Oracle分类⽬录。将固定连接加⼊收藏夹。
发布评论