更新表的方式有三种方法

更新表的方式有三种方法
2011-05-04 10:46

1、
其中最普通的是update t1 set b=(select b from t2 where t1.a=t2.a);
但是,要注意空值的影响,
如果怕空值的影响,要写成
update t1 set b= (select b from t2 where t1.a=t2.a)
where exists
(select 1 from t2 where t1.a=t2.a);


2、
update (
select /*+use_hash(t1,t2)*/ t1.b b1,t2.b b2
from t1,t2 where t1.a=t2.a)
set b1=b2;

这种方法效率高,但是要注意两个关联字段都要有唯一性索引!

这种方式我试过了,确实超级快。

3、存储过程

SQL> declare
2 cursor c is
3 select t1.*,t1.rowid from t1;
4 begin
5 for c1 in c loop
6 update t1 set b=
7 (select b from t2 where a=c1.a)
8 where rowid=c1.rowid
9 and
10 exists
11 (select 1 from t2 where c1.a=t2.a);
12 end loop;
13 end;
14 /

但是还是要注意要有exists的语句,否则一样解决不了空值问题

下面实验如下:

SQL> select * from t1;

A B
---------- ----------
1 1
2 2
3 4
4 4
1
2
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> update t1 set b=(select b from t2 where t1.a=t2.a);

已更新7行。

SQL> select * from t1;

A B
---------- ----------
1 2
2 5
3 7
4
1 2
2 5
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL>
现在ROLLBACK还原,还是原来表的记录如下,加EXISTS操作看看有什么变化
SQL> select * from t1;

A B
---------- ----------
1 1
2 2
3 4
4 4
1
2
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL> update t1 set b= (select b from t2 where t1.a=t2.a)
2 where exists
3 (select 1 from t2 where t1.a=t2.a);

已更新5行。

SQL> select * from t1;

A B
---------- ----------
1 2
2 5
3 7
4 4
1 2
2 5
4

已选择7行。

SQL> select * from t2;

A B
---------- ----------
1 2
2 5
3 7
6

SQL>


谢谢!
现在我实验明白了,如果不加
where exists
(select 1 from t2 where t1.a=t2.a);

t1表的a,b字段有4,4的一条记录,由于在t2表中a,b字段不存在a字段值为4的记录.这样在UPDATE的时候,在t2表中找不到就会用null 去UPDATE t1表的4,4为4,null,这可不是我们愿意看到的.

但加了那个EXISTS,问题就避免了。

除了NULL外,还有一个不得不说的地方,就是这样的UPDATE语句非常容易出现一个著名错误:ORA-01427: 单行子查询返回多行。大家看现在的ljb_test1表的id1列有重复记录,就是id1=2有两条记录

SQL> select * from ljb_test1;

  ID1                                ID2

--------------------------------------------------------------------

1                                   17

2                                   18

3                                   16

SQL> select  * from ljb_test2;

 ID1                                  ID2

--------------------------------------- ------------------------------

1                                    27

2                                    28

ljb_test1id1=2有两条重复记录,在ljb_test2中只有一条记录,这个时候如果利用ljb_test1去更新ljb_test2表,就立即报错如下:

SQL>  update ljb_test2 set id2=(select id2 from ljb_test1 where ljb_test2.id1=ljb_test1.id1)

  2    where exists (select 1 from ljb_test1 where ljb_test2.id1=ljb_test1.id1);

update ljb_test2 set id2=(select id2 from ljb_test1 where ljb_test2.id1=ljb_test1.id1)

     where exists (select 1 from ljb_test1 where ljb_test2.id1=ljb_test1.id1)

ORA-01427: 单行子查询返回多个行

不考虑NULL的写法也一样!

SQL>  update ljb_test2 set id2=(select id2 from ljb_test1 where ljb_test2.id1=ljb_test1.id1);

update ljb_test2 set id2=(select id2 from ljb_test1 where ljb_test2.id1=ljb_test1.id1)

ORA-01427: 单行子查询返回多个行

注意到,如果两表完全一致,ljb_test1.id1ljb_test2.id1一一对应,都是唯一的,那就不可能出现这样错误!如果两边不一致,ljb_test1id1值多余ljb_test2id1值或者反过来,就要特别注意了!讲白点就是,多的表允许用少的表来更新,被更新表可以不唯一,但是用来更新的表一定要是有唯一记录。

还要特别说明一点,如果需要更新的两边的结果集都有重复,那别管是利用ljb_test1更新ljb_test2还是利用ljb_test2更新ljb_test1,都休想成功了,要有这个意识!

总结:update操作的时候,要特别注意ORA-01427: 单行子查询返回多个行 的错误,两表更新的时候尽量是两表结果集都是不重复记录,如果有重复记录要注意更新的方向。

posted @ 2011-10-11 21:22  jex  阅读(450)  评论(0)    收藏  举报
点击右上角即可分享
微信分享提示