首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >关于ORA-01779问题的分析和解决 (r4笔记第22天)

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

作者头像
jeanron100
发布2018-03-15 15:08:06
1.2K0
发布2018-03-15 15:08:06
举报

最近同事问我一个问题,是关于一个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. 可以看到还是很快就处理完成了,而且也没有多次更新。基本是完成了期望的结果。 通过这个问题,方法总比困难多,一种方式不合适还可以通过其它的方式实现。能够最大限度的保证数据的准确性才是根本。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2015-01-21,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档