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，其他都没关联，粗心啊。全部关联上就好了。

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