最近同事问我一个问题，是关于一个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/，如需转载，请注明出处，否则将追究法律责任。
扫一扫
704
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
