更新表的方式有三种方法
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
--------------------------------------------------------------------
2
1 17
2 18
3 16
SQL> select * from ljb_test2;
ID1 ID2
--------------------------------------- ------------------------------
5
1 27
2 28
在ljb_test1中id1=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.id1和ljb_test2.id1一一对应,都是唯一的,那就不可能出现这样错误!如果两边不一致,ljb_test1的id1值多余ljb_test2的id1值或者反过来,就要特别注意了!讲白点就是,多的表允许用少的表来更新,被更新表可以不唯一,但是用来更新的表一定要是有唯一记录。
还要特别说明一点,如果需要更新的两边的结果集都有重复,那别管是利用ljb_test1更新ljb_test2还是利用ljb_test2更新ljb_test1,都休想成功了,要有这个意识!
总结:update操作的时候,要特别注意ORA-01427: 单行子查询返回多个行 的错误,两表更新的时候尽量是两表结果集都是不重复记录,如果有重复记录要注意更新的方向。
【推荐】2025 HarmonyOS 鸿蒙创新赛正式启动,百万大奖等你挑战
【推荐】博客园的心动:当一群程序员决定开源共建一个真诚相亲平台
【推荐】开源 Linux 服务器运维管理面板 1Panel V2 版本正式发布
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步