merge的使用演示

今天药忘吃喽~ 2022-06-10 22:39 288阅读 0赞

merge的使用演示  
1.首先建立目标表score(id,score)  
create table score(id varchar(10),score number(3));  
向目标中插入数据  
insert into score values(1000,88);  
insert into score values(1001,78);  
insert into score values(1002,50);  
insert into score values(1003,48);  
2.创建源表 student  
create table student(id varchar(10),name varchar(4),score number(3));  
insert into student values(1002,'jack',55);  
insert into student values(1003,'chen',78);  
insert into student values(1004,'www',54);  
insert into student values(1005,'peng',95);  
insert into student values(1006,'sana',66);  
3.执行merge之前查看目标表  
select \* from score;  
SQL> select \* from score;

ID                        SCORE  
\-------------------- ----------  
1001                         78  
1002                         50  
1003                         48

4.执行merge之前查看源表  
select \* from student;  
SQL> select \* from student;

ID                   NAME          SCORE  
\-------------------- -------- ----------  
1003                 chen             78  
1004                 www              54  
1005                 peng             95  
1006                 sana             66  
5.执行merge操作   
MERGE INTO score s  
USING student st --using指定数据源，数据源可以来自一个查询，一个表，也可以来自视图  
  on (s.id=st.id) \-- 指定一个条件，此条件决定是执行更新操作还是插入操作  
WHEN MATCHED THEN --只有满足on的条件才被更新  
UPDATE SET  
 s.score=st.score  
WHEN NOT MATCHED THEN \--不满足on的条件的记录才被插入  
insert (s.id,s.score) values (st.id,st.score);

SQL> MERGE INTO score s  
  2  USING student st  
  3    on (s.id=st.id)  
  4  WHEN MATCHED THEN  
  5  UPDATE SET  
  6   s.score=st.score  
  7  WHEN NOT MATCHED THEN  
  8  insert (s.id,s.score) values (st.id,st.score);

4 行已合并。

6.查看执行merge之后的目标表  
SQL> select \* from score;

ID                        SCORE  
\-------------------- ----------  
1001                         78  
1002                         50  
1003                         78  
1004                         54  
1005                         95  
1006                         66

已选择6行。  
ID为1003的这条记录被update了，ID为1004,1005,1006被insert到目标里了