ORA-01779: cannot modify a column which maps to a non-key-preserved table
Oracle中试图对一个子查询进行更新时可能会出现ORA-01779错误。该错误的内容为:
ORA-01779: cannot modify a column which maps to a non-key-preserved table
例如,使用以下的更新查询就会出现该错误。
CREATE TABLE test1 ( id integer primary key, num integer );
INSERT INTO test1 VALUES (1,0);
INSERT INTO test1 VALUES (2,0);
INSERT INTO test1 VALUES (3,0);
INSERT INTO test1 VALUES (4,0);
CREATE TABLE test2 ( id integer, num integer, upd integer );
INSERT INTO test2 VALUES (1,10, 0);
INSERT INTO test2 VALUES (2,20, 1);
UPDATE ( SELECT t1.id id1, t1.num num1, t2.id id2, t2.num num2
FROM test1 t1, test2 t2 WHERE t1.id=t2.id AND t2.upd=1 )
SET num1=num2; ORA-01779: cannot modify a column which maps to a non-key-preserved table
这个错误的意思是,子查询的结果中,更新数据源(test2)的内容不唯一,导致被更新对象(test1)中的一行可能对应数据源(test2)中的多行。 本例中,test2表的id不唯一,因此test2表中可能存在id相同但是num不相同的数据,这种数据是无法用来更新 test1 的。
解决方法就是保证数据源的唯一性,例如本例中可以为test2.id创建一个唯一索引:
CREATE UNIQUE INDEX test2_idx_001 ON test2 (id);
之后上面的更新就可以执行了。
另外也可以强制 Oracle 执行,方法是加上 BYPASS_UJVC 注释。
UPDATE
( SELECT /*+ BYPASS_UJVC */ t1.id id1, t1.num num1, t2.id id2, t2.num num2
FROM test1 t1, test2 t2
WHERE t1.id=t2.id AND t2.upd=1 )
SET num1=num2;
BYPASS_UJVC的作用是跳过Oracle的键检查。 这样虽然能够执行了,但是如果test2中存在不唯一的数据,test1就会被更新多次而导致意想不到的结果。
相关文章
- 【Docker 基础教程】Error: Failed to download metadata for repo ‘appstream‘: Cannot prepare internal mirror
- 使用pyspider框架出现Exception: HTTP 599: SSL certificate problem: unable to get local issuer certificate
- Maven异常Type Project configuration is not up-to-date with pom.xml. Run Maven->Update Project or use Quick Fix
- convert datetime to timestamp in ms sql server
- Java报错:java.math.BigDecimal cannot be cast to java.lang.String
- [AppDelegate window]: unrecognized selector sent to instance 0x600002b178e0
- Cannot connect to the Docker daemon. Is the docker daemon running on this host?
- According to TLD or attribute directive in tag file, attribute test does not accept any expressions
- maven2报xxxServlet cannot be cast to javax.servlet
- Type mismatch: cannot convert from MainFragment to Fragment 报错
- eclipse 提示错误**cannot be resolved to a type
- [Object Tracking] How to learn Active contour model - Snake Model
- HOW TO: Synchronize changes when completing a P2V or V2V with VMware vCenter Converter Standalone 5.1
- 开源规则引擎 Drools 学习笔记 之 -- 1 cannot be cast to org.drools.compiler.kie.builder.impl.InternalKieModule
- cannot be translated into a null value due to being declared as a primitive type. Consid
- find_if 仿函数错误:cannot convert 'this' pointer from 'const CActTaskData' to 'CActTaskData &'
- Netmap to VALE—— 从高性能网络框架到高性能虚拟网络交换机
- Oracle to_char格式化函数
- class path resource [spring/] cannot be resolved to URL because it does not exist
- ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.
- maven 从svn导入项目遇到的问题 No marketplace entries found to handle yuicompressor maven plugin:1.3.0:compile
- 已解决java.lang.ClassCastException: class java.lang.Integer cannot be cast to class java.lang.Long
- unable to connect to 127.0.0.1:62001: cannot connect to 127.0.0.1:62001: 由于目标计算机积极拒绝,无法连接。 (10061)
- ailed to send crash report due to a network error: SocketException: OS Error: 信号灯超时时间已到 , errno = 12
- How to make a custom WIDGET in OpenERP
- android.view.WindowManager$BadTokenException: Unable to add window -- token null is not for an appli
- OpenNURBS to OpenCASCADE
- 【错误记录】p7zip 交叉编译 Android 版本 NDK 报错 ( error: case value evaluates to -2 , which cannot be narrowed )