[20190826]update结果集2.txt

[20190826]update结果集2.txt --//补充对结果集的修改。以前做过类似测试,链接: --//http://blog.itpub.net/267265/viewspace-2139049/ 1.环境: SCOTT@test01p> @/ver1 PORT_STRING          VERSION    BANNER                                                                       CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production      0 create table t1 ( id number ,name  varchar2(20)); insert into t1 values (1,'a'); insert into t1 values (2,'b'); insert into t1 values (3,'c'); insert into t1 values (4,'d'); create table t2 ( id number ,name varchar2(20)); insert into t2 values (2,'bb'); insert into t2 values (3,'cc'); 2.查询: SCOTT@test01p> select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id; ID T1NAME ID T2NAME -- ------ -- -------  2 b       2 bb  3 c       3 cc SCOTT@test01p> create unique index pk_t1 on t1(id); Index created. SCOTT@test01p> create unique index pk_t2 on t2(id); Index created. SCOTT@test01p> alter table t1 add constraint pk_t1 primary key (id) enable validate; Table altered. SCOTT@test01p> alter table t2 add constraint pk_t2 primary key (id) enable validate; Table altered. 3.测试1: --//实现目的是id相同值使用,使用t2表的name替换原来的t1表的name值. SCOTT@test01p> update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t1name=t2name , t2name=t1name; update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t1name=t2name , t2name=t1name                                                                                                              * ERROR at line 1: ORA-01776: cannot modify more than one base table through a join view --//不能实现2个表同时修改. SCOTT@test01p> update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t1name=t2name ; 2 rows updated. SCOTT@test01p> select * from t1;         ID NAME ---------- --------------------          1 a          2 bb          3 cc          4 d SCOTT@test01p> select * from t2;         ID NAME ---------- --------------------          2 bb          3 cc SCOTT@test01p> rollback ; Rollback complete. 4.测试2: --//实现目的是id相同值使用,使用t1表的name替换原来的t2表的name值. SCOTT@test01p> update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t2name=t1name ; 2 rows updated. SCOTT@test01p> select * from t1;         ID NAME ---------- --------------------          1 a          2 b          3 c          4 d SCOTT@test01p> select * from t2;         ID NAME ---------- --------------------          2 b          3 c --//OK.这样也可以.实际上这样的操作仅仅修改一个表. --//这样操作既简单也不容易出错.update的是结果集,而且多数情况先查询看看.前提条件是通过主键连接两个表. 5.删除T2表主键看看: SCOTT@test01p> alter table t2 drop constraint pk_t2 ; Table altered. SCOTT@test01p> drop index pk_t2 ; Index dropped. --//再重复上面测试: SCOTT@test01p> update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t1name=t2name ; update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t1name=t2name                                                                                              * ERROR at line 1: ORA-01779: cannot modify a column which maps to a non key-preserved table $ oerr ora 01779 01779, 00000, "cannot modify a column which maps to a non key-preserved table" // *Cause: An attempt was made to insert or update columns of a join view which //         map to a non-key-preserved table. // *Action: Modify the underlying base tables directly. SCOTT@test01p> update (select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id) set t2name=t1name ; 2 rows updated. SCOTT@test01p> select * from t2;         ID NAME ---------- --------------------          2 b          3 c SCOTT@test01p> select t1.id,t1.name t1name ,t2.id ,t2.name t2name from t1,t2 where t1.id=t2.id;         ID T1NAME                       ID T2NAME ---------- -------------------- ---------- --------------------          2 b                             2 b          3 c                             3 c SCOTT@test01p> rollback ; Rollback complete. --//这样仅仅修改T2表上记录.