[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表上記錄.