Oracle 一个表更新另一个表

1 创建表结构 test1 

create table test1(  id varchar2(10) ,  val varchar2(20),  name varchar2(20)); 

2 创建表结构 test12

create table test2(  id varchar2(10),  val varchar2(20) ,  name varchar2(20) );

3 添加数据

insert into test1 values ('1','1111','gsdg');
insert into test1 values ('2','222vs','dfgd');  
insert into test1 values ('2','2222sdf','dg');    
insert into test1 values ('3','33sdf33','dgd');  
insert into test1 values ('4','44d44','dgdfg');
insert into test1 values ('6','66dgd66','hhfg');
commit;  
insert into test2 values ('1','aa332aa','77');
insert into test2 values ('2','bb34b3b','7538');      
insert into test2 values ('3','c34ccc','835'); 
insert into test2 values ('4','dd34dd','3255');
insert into test2 values ('5','ee34e','596666');
commit; 

添加结果：

3.1    3.2

 

4 更新数据

merge into test1 t using test2 s
 on (t.id = s.id) --执行条件 
 when matched then --如果数据存在,更新数据 
 update set t.val = s.val,t.name=s.name 
 when not matched then --如果数据不存在,插入数据 
 insert (t.id,t.val,t.name) values (s.id,s.val,s.name);

5 更新结果

更新结果：

5.1   5.2