Oracle中 如何用一个表的数据更新另一个表中的数据

准备阶段

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并不会）

参考：Oracle中用一个表的数据更新另一个表的数据

Group by 中avg();sum();min();max();count();的运用整理(Oracle的执行顺序)