准备阶段
1.建表语句:
create table table1(
idd varchar2(10) ,
val varchar2(20)
);
create table table2(
idd varchar2(10),
val varchar2(20)
);
2.插入数据:
insert into table1 values ('01','1111');
insert into table1 values ('02','222');
insert into table1 values ('02','2222');
insert into table1 values ('03','3333');
insert into table1 values ('04','4444');
insert into table1 values ('06','6666');
commit;
insert into table2 values ('01','aaaa');
insert into table2 values ('02','bbbb');
insert into table2 values ('03','cccc');
insert into table2 values ('04','dddd');
insert into table2 values ('05','eee');
commit;
3.两张表如下图:
要将 table2中idd - val 的值,赋值给table1对应的 idd - val;
为了验证操作的合理性,设置了如下几个需要额外考虑情况:
注意两表特殊地方在于:
- table1中,有1条
idd
字段值为06
的数据,table2中idd
字段没有06
,命名为e1
; - table1中,有2条
idd
字段值都为02
,并且对应的val
不同的数据,命名为e2
,以下都能正常解决此情况; - table2中,有2条
idd
字段值都为05
,但对应的val
值不同的数据,命名为e3
,待添加;
sql查询:
1. 最容易想到的办法:通过子查询 ,直接 update
,如下:
update table1 set table1.val = (select val from table2 where table1.idd = table2.idd);
- 问题:我们遇到了
e1
情况,即table1中06
对应的值被改变了-->val变成了null(即图中的空白);
这并不是我们的本意,故做出如下改进。
2. 加入限制条件,对于 table1中有值,但是table2中无值的idd
字段,不做修改;
update table1 set val = (select val from table2 where table1.idd = table2.idd) where exists (select 1 from table2 where table1.idd = table2.idd)
- 第2种写法看似没问题,但如果我们再次向table2中插入一条数据,
insert into table2 values ('03','ccc');
遇到了e3
情况, - 执行后会报错如下:
ORA-01427:单行子查询返回多个行
3. 通过上述分析,简单的更新语句并不能解决遇到的异常情况。所以我们可以使用merge
,如下:
merge into table1 using table2 on (table1.idd = table2.idd) when matched then update set table1.val = table2.val
- 虽然可以解决
e1
情况,然而遇到e3
情况时,仍然报错,如下:
ORA-30926: 无法在源表中获得一组稳定的行
4. 最后,在3的基础上,加入限制条件,即可解决;
merge into table1 using (select t.idd ,max(t.val) m from table2 t group by t.idd)table2 on (table1.idd = table2.idd) when matched then update set table1.val = table2.m
- 上述写法在
using
后面构造了一个新的table2,但一定要对val做出处理,如果是varchar类型,可以选择 max,min等函数,如果number类型,可以使用sum,avg等函数,总之,要对val做出处理(对应多个的时候,到底要哪个?最大的还是最小的),新的table2是一个idd对应一个val。 - 为什么构造新的table2时要加
group by t.idd
,因为select max(t.val) m from table2 t
查询的是一条数据,t.idd
不属于这条数据的任何字段,故select t.idd
后报错,拼接group by t.idd
便可以查出需要的idd字段。(针对oracle数据库,mysql并不会)