`
13594135
  • 浏览: 193504 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

oralce多表更新方法

阅读更多
--备份
create table content_20110112 as select * from content;

alter table content_20110112 add constraint content_20110112_key primary key(contid);

--回退
update content  t
set t.products = (select o.products from content_20110112 o where o.contid = t.contid)
where t.object_id in('11111111000000010000000000000896');

--回退方法二

update (
select /*+ BYPASS_UJVC */ t.contid t1,o.contid t2,t.products as p1 , o.products as p2
from content_20110112 o join content t on t.contid = o.contid
--where t.object_id in('11111111000000010000000000000896')
) xx
set  p1 = p2 

BYPASS_UJVC的作用是跳过Oracle的键检查。



引用
Oracle中Update的一个写法,注意异常ORA-01779
---------------------------

这篇是转的,大家在写SQL的时候特别注意不要犯此类ORA-01779错误.

-- 一、==========
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 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就会被更新多次而导致意想不到的结果。

--二、========================

SQL> create table a ( id int, a1 varchar2(25) );
SQL> create table b ( id int, b1 varchar2(25) );
SQL> insert into a values ( 1, 'Hello' );
SQL> insert into a values ( 2, '**xx' );
SQL> insert into b values ( 2, 'World' );
SQL> commit;


SQL> update ( select a1, b1 from a, b where a.id = b.id )
  2  set a1 = b1;
set a1 = b1
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

--无法Update,必须要有一个主键

SQL> alter table b add constraint b_key primary key(id);
SQL> update ( select a1, b1 from a, b where a.id = b.id )
  2  set a1 = b1;

1 row updated.

--可以Update


SQL> update ( select a1, b1 from a, b where a.id = b.id )
  2  set b1 = a1;
set b1 = a1
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

--交换位置后依旧无法更新

SQL> alter table b drop constraint b_key;
SQL> alter table a add constraint a_key primary key(id);
SQL> update ( select a1, b1 from a, b where a.id = b.id )
  2  set b1 = a1;

1 row updated.

--为表a设置主键后可以更新

SQL> alter table a drop constraint a_key;
SQL> alter table a add constraint a_key primary key(id,a1);
SQL> update ( select a1, b1 from a, b where a.id = b.id )
  2  set b1 = a1;
set b1 = a1
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

--使用联合主键仪不可以的,必须是关联字段


    由上可知,使用这种方式来更新表,需要用于更新的表(最终数据表)的关联字段必须设置为主键,且不可多字段主键。另外还有一个网友也指出了另外一个问题:
If the user has update permission on table A, but only has select permission on table B, they cannot update via the first example.  Oracle will return ORA-01031 (insufficient privileges).

    测试一下:

SQL> create user x identified by x;
SQL> grant create session on x;
SQL> grant select,update on a to x;
SQL> grant select on b to x;

SQL> create public synonym a for wangxiaoqi.a;
SQL> create public synonym b for wangxiaoqi.b;

SQL> conn x/x
Connected.
SQL> update ( select a1, b1 from a, b where a.id = b.id )
  2  set a1 = b1;
update ( select a1, b1 from a, b where a.id = b.id )
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

--系统报错权限不够

SQL> update a set a1 = (select b1 from b where b.id=a.id);

2 rows updated.

--使用Update...Select...语句是可以更新成功的


--三、===========================
ORA-01779问题的解决作者:coordinator  来源:博客园

昨天帮一个师弟看一个ORACLE触发器T的问题,这个触发器建在视图V上,当用户往视图里插记录的时候就自动插到视图的组成表里。但是每次插入总提示,ORA-01779 cannot modify a column which maps to a non key-preserved table。而怪异的是,有一个视图V2和V的构成方式一摸一样,但是它的触发器T2就没问题。在网上找了很多帖子,绝大部分原因都是插入的基本表记录不唯一,我检查了触发器内容和基本表的keys,都没问题。

到第二天的时候突然看到一个帖子的原因解释:

view的更改是有限制的

General Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.

UPDATE Rule
All updatable columns of a join view must map to columns of a key-preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.

DELETE Rule
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.

INSERT Rule
An INSERT statement must not explicitly or implicitly refer to the columns of a non-key preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.

(http://www.itpub.net/225291.html)

再检查了触发器,发现事件那块只关联了UPDATE,其他都没关联,粗心啊。全部关联上就好了。

看来触发器的几个选项一定要好好检查啊。



分享到:
评论

相关推荐

    ORACLE多表关联的update语句

    在Oracle数据库中,多表关联的UPDATE语句用于更新一个表中的数据,这些数据依赖于另一个或多个表的记录。这种操作在数据整合、错误修正或者数据同步时非常常见。以下我们将详细探讨如何使用不同方式执行多表关联的...

    Oracle两张表关联批量更新其中一张表的数据

    方法一(推荐): UPDATE 表2 ...尤其注意最后的外层where条件尤为重要,是锁定其批量更新数据的范围。 方法二: MERGE INTO 表2 USING 表1 ON (表2.A = 表1.A) -- 条件是 A 相同 WHEN MATCHED THEN UP

    oracle两表关联更新Demo

    本文将通过一个具体的例子来介绍如何在Oracle中实现两表之间的关联更新,并重点讲解使用`EXISTS`嵌套子查询的方法。 #### 二、示例代码分析 以下为示例代码: ```sql UPDATE gwzjjk.zj_bankaccount a SET a....

    oracle普通表转化为分区表的方法

    Oracle数据库中的分区表是一种高级的表组织形式,它将大表分成多个较小的部分,每个部分称为一个分区,以提高查询性能和管理效率。当表的大小超过2GB时,官方推荐使用分区表,因为它们在处理大量数据时具有显著优势...

    Oracle 临时表用法

    本文将详细介绍Oracle临时表的创建方法、使用场景以及优缺点,帮助读者更好地理解和运用这一特性。 #### 二、Oracle临时表概述 Oracle临时表是一种特殊的表,用于存储临时数据,通常用于事务处理或特定会话期间的...

    ORACLE批量更新四种方法.txt ORACLE批量更新四种方法.txt

    在实际应用中,选择合适的批量更新方法对于提高系统的整体性能至关重要。不同的方法有着各自的适用场景和优缺点,开发者应根据具体情况灵活选用。同时,考虑到Oracle数据库的特点,合理利用其提供的各种工具和技术...

    Oracle数据库分区表操作方法

    Oracle数据库分区表操作方法 Oracle数据库中的分区表是将大型数据表分割成多个小表,以提高应用系统的性能和方便数据管理。在本文中,我们将详细介绍分区表的使用方法和优点。 分区表的优点 使用分区表可以带来...

    oracle 普通表转分区表方式

    本文详细介绍了将 Oracle 普通表转换为分区表的方法,包括备份创建表脚本、查看数据类型、创建分区表、迁移数据和更新索引和约束等步骤。通过将普通表转换为分区表,可以提高查询效率、减少存储空间和提高数据管理...

    Oracle分区表详解

    通过将一个大型的表或者索引分割成多个较小且可管理的部分,分区技术能够显著提升数据库的性能、可用性和可维护性。 ##### 分区的作用: 1. **增强可用性**:即使表的一部分出现故障,其他部分的数据依然可以被...

    ORACLE 批量更新三种方式比较

    针对这一需求,本文将详细介绍并比较三种常用的批量更新方法:通过PL/SQL循环更新、使用单条SQL语句更新以及采用子查询的方式进行更新。 #### 一、通过PL/SQL循环更新 这种方式是通过定义一个游标(Cursor),然后...

    Oracle分区表用法

    - 表空间(Tablespace):在Oracle数据库中,表空间是存储数据的主要容器,由一个或多个数据文件组成。所有的数据库对象,尤其是表,都存储在特定的表空间内。表空间允许数据库管理员管理存储资源,确保数据的合理...

    Oracle 11i 表结构

    ### Oracle 11i 表结构详解 #### 一、Oracle 11i 概述 ...通过深入理解表结构、关系以及触发器、弹性域等高级特性的使用方法,开发者可以更好地利用Oracle 11i的强大功能,构建出高效稳定的应用系统。

    oracle全表扫描的3种优化手段

    在实际应用中,可以根据具体情况综合运用以上三种方法来优化Oracle中的全表扫描性能: 1. **评估表大小和查询频率**:对于频繁查询的大表,优先考虑使用并行查询和调整多块读取参数;对于不经常访问的小表,则可能只...

    Oracle数据库表建立字段唯一性的方法

    以下是对"Oracle数据库表建立字段唯一性方法"的详细解释: 1. **唯一约束(Unique Constraint)** - **定义**:唯一约束是一种表级约束,用于确保表中的某列或某些列的值都是唯一的。这意味着,除了NULL值之外,...

    Oracle数据表更新即时通告的实现方法.pdf

    本文主要讨论如何在Oracle数据库中实现数据表更新的即时通告功能,尤其适用于数据量大、多客户端同时在线、实时性要求高的情况。 传统的通告方法有两种:被动方式和主动方式。被动方式是由客户端定时向服务器查询...

    Oracle分区表培训

    而复合分区则结合了两种或更多分区方法。创建分区表时,需要指定分区键、分区类型以及具体的分区边界或列表值。 接着,"2.分区索引示例.sql"探讨了与分区相关的索引策略。在分区表上创建索引可以进一步提升查询效率...

    ORACLE SYSTEM表空间已满解决方案.docx

    当表空间占满时,可能会导致新的数据无法被插入或更新,从而影响到数据库的正常运行。常见的原因包括但不限于: - **数据增长过快**:随着业务的发展,数据量迅速增加,导致存储空间不足。 - **未及时清理旧数据**...

    oracle 移动表空间

    在Oracle数据库管理系统中,移动表空间是数据库管理员经常需要进行的一项任务,这通常涉及到数据文件、控制文件以及联机重做日志文件的位置迁移。以下是对这些知识点的详细解释: 1. **移动数据文件**: - **ALTER...

    更改Oracle表中字段的顺序的终极方法

    ### 更改Oracle表中字段顺序的方法 在Oracle数据库管理中,有时我们可能需要对表中的字段顺序进行调整。然而,通常的做法是通过备份、删除原表并重建来达到这一目的,这种方法不仅耗时而且繁琐。幸运的是,经过一番...

    对Oracle多表关联更新的应用研究.pdf

    Oracle数据库在多表关联更新中的应用研究主要集中在如何高效、准确地处理复杂的数据操作,尤其在涉及多个表之间关系的数据更新场景。在这个案例中,研究背景是一家名为仪刘鑫梓的大型百货连锁销售公司,该公司正在...

Global site tag (gtag.js) - Google Analytics