--备份
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 

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

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