oracle 可更新结果集,[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表上记录.