做数据仓库经常遇到的问题,大家先看下面的例子 表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可能被更新多次.