关于ORA-01779问题的分析和解决

最近同事问我一个问题，是关于一个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.

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1410058/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1410058/