关于ORA-01779问题的分析和解决 (r4笔记第22天)

最近同事问我一个问题,是关于一个update语句的问题,需求有点特别,结果在使用update语句尝试了各种方法后,仍然是不依不饶的报出ORA-01779的错误。今天专门花时间分析了一下这个问题,还是有些收获。 为了说明问题,我们先来看两组数据 有两个表data,link,现在希望更新data表中的amount列值,和link表的credit_class做关联。 如果data.credit_class=link.credit_class,就直接把data.amount修改为link.score。 逻辑的难点是,如果data.credit_class!=link.credit_class就把data.amount修改为link.credit_class='*'对应的score值 以下面的数据为例, 对于id为1的数据来说,data.credit_class在link.credit_class中存在,所以就需要把amount从110修改为100 对于id未9的数据来说,data.credit_class在link.credit_class中不匹配,所以就找到link.credit_class='*'的值1,然后把data.amount的值从9修改为1 SQL> select *from data; ID CREDIT_CLASS AMOUNT ---------- ------------------------------ ---------- 1 vip 110 2 vip 110 3 vip 110 4 vip 110 5 vip 110 6 vip 110 7 vip 110 8 vip 110 9 normal 9 10 normal 9 10 rows selected. SQL> select *from link; CREDIT_CLASS SCORE ------------------------------ ---------- vip 100 agent 10 * 1 关于关联update的问题,比较经典的错误就是 ERROR at line 11: ORA-01779: cannot modify a column which maps to a non key-preserved table 问题的解释如下: SQL> !oerr ora 01779 01779, 00000, "cannot modify a column which maps to a non key-preserved table" // *Cause: An attempt was made to insert or update columns of a join view which // map to a non-key-preserved table. // *Action: Modify the underlying base tables directly.

我们来首先复现一下这个问题,准备基础的数据。 create table data (id number,credit_class varchar2(100),amount number); create table link (credit_class varchar2(100),score number); insert into link values('vip',100); insert into link values('agent',10); insert into link values('*',1); begin for i in 1..8 loop insert into data values(i,'vip',110); end loop; end; / begin for i in 9..10 loop insert into data values(i,'normal',9); end loop; end; / alter table data modify(id primary key); --给表data加上主键,可以保证在关联update能够校验唯一性。 有些数据的credit_class匹配link.credit_class,有些不匹配。我们先一股脑把数据都配上,然后再过滤。 select a.id,a.credit_class,x.credit_class ,a.amount,x.score From data a, link x where (a.credit_class=x.credit_class or x.credit_class ='*') order by id; ID CREDIT_CLASS CREDIT_CLASS AMOUNT SCORE ---------- ------------------------------ ------------------------------ ---------- ---------- 1 vip vip 110 100 1 vip * 110 1 2 vip vip 110 100 2 vip * 110 1 3 vip * 110 1 3 vip vip 110 100 4 vip * 110 1 4 vip vip 110 100 5 vip * 110 1 5 vip vip 110 100 6 vip vip 110 100 6 vip * 110 1 7 vip vip 110 100 7 vip * 110 1 8 vip vip 110 100 8 vip * 110 1 9 normal * 9 1 10 normal * 9 1 看看id=1和id=9的数据,就能看出差别了。id=1的数据存在匹配的credit_class,而id=9却没有匹配的ccredit_class,所以关联表link之后得到的score也不同。 现在的问题是如何把id=1的数据进行去重。如果存在匹配的credit_class,就修改对应的score为amount值,对于credit_class='*’ and id=1的那条记录如何做排除是这个问题的关键。 尝试了各种方法之后,发现还是把数据一分为二,creidt_class匹配的一组,credit_class不匹配的一组。根据count(id) 做group by来分组。 --存在匹配credit_class的数据为: ID OLD CREDIT_CLASS AMOUNT SCORE ---------- ------------------------------ ------------------------------ ---------- ---------- 1 vip vip 110 100 6 vip vip 110 100 2 vip vip 110 100 4 vip vip 110 100 5 vip vip 110 100 8 vip vip 110 100 3 vip vip 110 100 7 vip vip 110 100 8 rows selected. --不匹配credit_class的数据为: select *from(select x.id,a.credit_class old,x.credit_class ,x.amount,a.score From link a, data x where (a.credit_class=x.credit_class or a.credit_class ='*') ) where id in( select id from ( select x.id,a.credit_class old,x.credit_class ,a.score From link a, data x where (a.credit_class=x.credit_class or a.credit_class ='*') )group by id having count(*)=1 ); 得到的数据如下: ID OLD CREDIT_CLASS AMOUNT SCORE ---------- ------------------------------ ------------------------------ ---------- ---------- 9 * normal 9 1 10 * normal 9 1 得到了数据集,但是使用update语句却是困难重重。 无论是使用Hint /*+BYPASS_UJVC*/ 还是使用各种可用的update方法。都会抛出ORA-01779: cannot modify a column which maps to a non key-preserved table SQL> update ( 2 select*from ( 3 select x.id,a.credit_class old,x.credit_class ,x.amount ,a.score From link a, data x 4 where (a.credit_class=x.credit_class or a.credit_class ='*') ) 5 where id in( 6 select id from ( 7 select x.id,a.credit_class old,x.credit_class ,a.score From link a, data x 8 where (a.credit_class=x.credit_class or a.credit_class ='*') 9 )group by id having count(*)=2 10 ) and old!='*' 11 ) set amount=score 12 / ) set amount=score * ERROR at line 11: ORA-01779: cannot modify a column which maps to a non key-preserved table 最后使用的方法是通过pl/sql来完成,写了一个小的pl/sql脚本。

  declare
 cursor part1_cursor is select*from (
  select x.id,a.credit_class old,x.credit_class ,x.amount ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')  )
  where id in(
  select id from (
select x.id,a.credit_class old,x.credit_class  ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')    
 )group by id   having count(*)=2
  ) and old!='*'
 ;
 cursor part2_cursor is 
  select *from(select x.id,a.credit_class old,x.credit_class  ,x.amount,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')  )
  where id in(
  select id from (
select x.id,a.credit_class old,x.credit_class  ,a.score From link a, data x
  where (a.credit_class=x.credit_class or a.credit_class ='*')    
 )group by id   having count(*)=1
  );
 begin
 for tmp_part1_cursor in part1_cursor loop
 dbms_output.put_line('update data set amount is '||tmp_part1_cursor.score||' for id:'||tmp_part1_cursor.id);
 update data set amount=tmp_part1_cursor.score where id=tmp_part1_cursor.id;
  rollback;
 end loop;
  for tmp_part2_cursor in part2_cursor loop
 dbms_output.put_line('update data set amount is '||tmp_part2_cursor.score||' for id:'||tmp_part2_cursor.id);
 update data set amount=tmp_part2_cursor.score where id=tmp_part2_cursor.id;
  rollback;
 end loop;
 end;
 /

脚本运行结果如下: update data set amount is 100 for id:1 update data set amount is 100 for id:6 update data set amount is 100 for id:2 update data set amount is 100 for id:4 update data set amount is 100 for id:5 update data set amount is 100 for id:8 update data set amount is 100 for id:3 update data set amount is 100 for id:7 update data set amount is 1 for id:9 update data set amount is 1 for id:10 PL/SQL procedure successfully completed. 可以看到还是很快就处理完成了,而且也没有多次更新。基本是完成了期望的结果。 通过这个问题,方法总比困难多,一种方式不合适还可以通过其它的方式实现。能够最大限度的保证数据的准确性才是根本。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-01-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Ryan Miao

Mybatis-update - 数据库死锁 - 获取数据库连接池等待

最近学习测试mybatis,单个增删改查都没问题,最后使用mvn test的时候发现了几个问题: update失败,原因是数据库死锁 select等待,原因是c...

45850
来自专栏Java3y

Hibernate【映射】知识要点

前言 前面的我们使用的是一个表的操作,但我们实际的开发中不可能只使用一个表的…因此,本博文主要讲解关联映射 集合映射 需求分析:当用户购买商品,用户可能有多个地...

32170
来自专栏牛客网

京东提前批研发面经

【每日一语】真实人生中,我们往往在大势底定无可更改时才迟迟进场,却又在胜败未分的浑沌中提早离席。——翁贝托·埃科《开头与结尾》

12220
来自专栏iOS开发日记

Object-C特性埋点

Objective-C是一门简单的语言,95%是C。只是在语言层面上加了些关键字和语法。真正让Objective-C如此强大的是它的运行时。它很小但却很强大。它...

45760
来自专栏公有云大数据平台弹性 MapReduce

yarn公平调度详细分析(一)

FairScheduler是yarn常用的调度器,但是仅仅参考官方文档,有很多参数和概念文档里没有详细说明,但是这些参明显会影响到集群的正常运行。本文的主要目的...

1.8K170
来自专栏前端

使用jOrgChart插件实现组织架构图的展示

(3)使用jOrgChart插件,根据返回的数据将其子节点加入到相应的<li></li>中。

99950
来自专栏生信宝典

基因组分析中多物种同源基因的鉴定和筛选

OrthoMCL能做什么 Orthologs are homologs separated by speciation events. Paralogs are...

43970
来自专栏程序员宝库

基于 Egg.js 框架的 Node.js 服务构建之用户管理设计

近来公司需要构建一套 EMM(Enterprise Mobility Management)的管理平台,就这种面向企业的应用管理本身需要考虑的需求是十分复杂的,...

1.6K40
来自专栏24K纯开源

用Qt写软件系列一:QCacheViewer(浏览器缓存查看器)

介绍      Cache技术广泛应用于计算机行业的软硬件领域。该技术既是人们对新技术探讨的结果,也是对当前软硬件计算能力的一种妥协。在浏览器中使用cache技...

28050
来自专栏生信宝典

OrthoMCL鉴定物种同源基因 (安装+使用)

1.2K70

扫码关注云+社区

领取腾讯云代金券