转载

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》原因分析和解决

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


原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 分类目录。将固定连接加入收藏夹。



正文到此结束
Loading...