解决ORA-01779错误

解决ORA-01779错误

做数据仓库经常遇到的问题,大家先看下面的例子
表test1
create table test1
(
 id number,
 gender varchar2(10)
);

insert into test1 values(1,'男');
insert into test1 values(2,'女');
insert into test1 values(3,'女');
insert into test1 values(4,'女');

表test2
create table test2
(
 id number,
 gender varchar2(10)
);

insert into test2 values(2,'男');
insert into test2 values(3,'男');
insert into test2 values(5,'女');

现在情况如下,test1与test2中ID相同的,我想用test2中的gender更新test1中的gender,使用下列语句
UPDATE
(
  SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
  A.ID = B.ID
)
SET AG = BG;

ERROR AT LINE 6:
ORA-01779: CANNOT MODIFY A COLUMN WHICH MAPS TO A NON KEY-PRESERVED TABLE

这个错误产生的原因是子查询的结果中,用来更新test1的test2表id不唯一,导致被更新对象test1中的一行可能要对应test2中的很多行,这样oracle就不知道该怎么更新
例如test1中id 1 '男'有可能对应test2中id 1 '男', id 1 '女'这2个数据,所以导致无法更新.

解决办法
1.给test2加上一个主键.
SQL> ALTER TABLE TEST2 ADD PRIMARY KEY(ID);

TABLE ALTERED.

SQL> UPDATE
  2  (
  3    SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
  4    A.ID = B.ID
  5  )
  6  SET AG = BG;

2 ROWS UPDATED.

SQL> ALTER TABLE TEST2 DROP PRIMARY KEY;

TABLE ALTERED.

2.给test2加上一个唯一索引
SQL> CREATE UNIQUE INDEX IDX_TEST2 ON TEST2(ID);

INDEX CREATED.

SQL> UPDATE
  2  (
  3    SELECT A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
  4    A.ID = B.ID
  5  )
  6  SET AG = BG;

2 ROWS UPDATED.

SQL> DROP INDEX IDX_TEST2;  

INDEX DROPPED.

3.使用BYPASS_UJVC强制执行,BYPASS_UJVC可以跳过ORACLE的键检查.
SQL> UPDATE
  2  (
  3    SELECT /*+BYPASS_UJVC*/ A.ID AI,A.GENDER AG,B.ID BI,B.GENDER BG FROM TEST1 A,TEST2 B WHERE
  4    A.ID = B.ID
  5  )
  6  SET AG = BG;

2 rows updated.
虽然这样可以执行,但是test2中存在不唯一的数据,所以test1可能被更新多次.