ORA-01779: cannot modify a column which maps to a non key-preserved table
来源:互联网 发布:什么是软件过程规范 编辑:程序博客网 时间:2024/04/29 00:32
当更新一个子查询的时候,会出现这个错误。ORA-01779: cannot modify a column which maps to a non key-preserved table
下面先模拟一下这个错误:
SQL> select * from gw1;
ID NAME
---------- ----------
1 a
2 b
3 c
SQL> select * from gw2;
ID UPDATE_ID
---------- ----------
1 11
2 22
3 33
4 44
SQL> select gw1.id,gw2.update_id from gw1,gw2 where gw1.id=gw2.id;
ID UPDATE_ID
---------- ----------
1 11
2 22
3 33
SQL> update (select gw1.id,gw2.update_id from gw1,gw2 where gw1.id=gw2.id) tmp set tmp.id=tmp.update_id;
update (select gw1.id,gw2.update_id from gw1,gw2 where gw1.id=gw2.id) tmp set tmp.id=tmp.update_id
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
产生这个错误的原因是因为gw2表的id不是唯一的,有可能gw1的一个id会对应多个gw2的id,这样就不知道怎么更新了。即使gw2的表中并不存在这样的记录。
解决方法:给gw2表的id列加上unique约束或设置为主键
SQL> alter table gw2 modify id unique;
Table altered.
SQL> update (select gw1.id,gw2.update_id from gw1,gw2 where gw1.id=gw2.id) tmp set tmp.id=tmp.update_id;
3 rows updated.
SQL> select * from gw1;
ID NAME
---------- ----------
11 a
22 b
33 c
如需转载,请注明出处:http://blog.csdn.net/nanaranran/article/details/19820425
- ORA-01779: cannot modify a column which maps to a non-key-preserved table
- 解决错误ORA-01779: cannot modify a column which maps to a non-key-preserved table
- ORA-01779: cannot modify a column which maps to a non-key-preserved table分析
- ORA-01779: cannot modify a column which maps to a non key-preserved table
- ORA-01779: cannot modify a column which maps to a non key-preserved table
- ORA-01779: cannot modify a column which maps to a non-key-preserved table
- ORA-01445: Cannot Select ROWID from a Join View without a Key-Preserved Table
- ora-01779 non-key-preserved table错误及/*+ BYPASS_UJVC*/ 的使用技巧
- 在视图或者子查询中查询rowid出错提示cannot select ROWID from a join view without a key-preserved table
- 关于 ORA-22905: cannot access rows from a non-nested table item
- linq insert The null value cannot be assigned to a member with type System.Int64 which is a non-null
- How to partition a non-partitioned table
- Add a column to table(Handling)
- 什么是Oracle Key-Preserved Table
- MySQL How to add a column in a table?
- Cannot make a static reference to the non-static method
- Cannot make a static reference to the non-static method
- Cannot make a static reference to the non-static field
- ps 命令详解
- 质量管理 六西格玛-黑带大师
- CRM Plugin调试
- MFC 控件大小随窗体改变而改变大小
- cocos2d-x内存管理机制解析
- ORA-01779: cannot modify a column which maps to a non key-preserved table
- paip.提高效率---微信 手机app快速开发平台—微网络撬动大市场
- mysql 截取字符串
- 深度解析 QT 编译安装方法
- GetMessage和PeekMessage的区别
- 冒泡排序
- android--半透明
- Java基本数据对应的封装类
- 北京某公司一份android面试题