[20170513]update结果集.txt

[20170513]update结果集.txt

--//前一阵子要对收费价格进行大量调整,当时开发随手写的sql非常不好,我建议通过结果集来修改,这样简单不容易出错.
--//通过例子说明:

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');

--//实现目的是id相同值使用,使用t2表的name替换原来的t1表的name值.

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


--//直接使用update对结果集修改:

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 3:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SCOTT@test01p> host 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.

--//主要问题我现在2个表没有主键.建立主键就ok了.
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.

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> rollback ;
Rollback complete.

--//这样操作既简单也不容易出错.update的是结果集,而且多数情况先查询看看.前提条件是通过主键连接两个表.

3.注意一些开发这样写是错误的.
SCOTT@test01p> update t1 set name =(select name from t2 where t1.id=t2.id);
4 rows updated.

--//我以前就遭遇过这样的错误,小心小心!!修改4条记录.
SCOTT@test01p> select * from t1;
        ID NAME
---------- --------------------
         1
         2 bb
         3 cc
         4
SCOTT@test01p> rollback ;
Rollback complete.

--//ID=1,4 的name被替换为NULL.必须满足在t2表存在的id修改.应该写成如下:

SCOTT@test01p> update t1 set name =(select name from t2 where t1.id=t2.id) where exists(select name from t2 where t1.id=t2.id);
2 rows updated.

SCOTT@test01p> select * from t1;
        ID NAME
---------- --------------------
         1 a
         2 bb
         3 cc
         4 d

SCOTT@test01p> rollback ;
Rollback complete.

4.还有一种就是使用merge,我觉得我们开发很少使用merge.我自己也很少用.还是上面的方法简单实用,不会出错.
--//而且我们的程序非常垃圾,经常要求开发修改后台业务数据.我经常先查询出结果,然后写成如下例子:

update (select * from dept where deptno=10) set loc='xxxx';