Oracle merge into usage and Examples

The following articles mainly introduce the usage of Oracle merge into and related examples. First, let's start with adding MERGE to Oracle 9I. The following is an analysis of the specific content of the article, I hope that you will be able to provide some help in this aspect after browsing.

Syntax:

 
 

  
  
MERGE [hint] INTO [schema .] table [t_alias]  
  
  
USING [schema .] { table | view | subquery } [t_alias]  
  
  
ON ( condition )  
  
  
WHEN MATCHED THEN merge_update_clause  
  
  
WHEN NOT MATCHED THEN merge_insert_clause;  
  
  
 
 
 

Create a test data table:

 
 

  
  
create table tj_test(id number,name varchar2(20),age number); 
 
 

Insert data into the table:

 
 

  
  
insert into tj_test values (1,'jan',23);  
  
  
insert into tj_test values (2,'kk',22);  
  
  
insert into tj_test values (3,'joe',27);  
  
  
select * from tj_test;  
 
 

The query result is as follows:

1 jan 23

2 kk 22

3 joe 27

Create another table

 
 

  
  
create table tj_test1 as select * from tj_test where 1=0 
 
 

Insert a data entry

 
 

  
  
insert into tj_test1 values (1,'jlk',23);  
  
  
select * from tj_test1  
 
 

The query result is as follows:

1 jkl 23 -- note that the value in the NAME field here is jkl

Oracle merge into uses MERGE. If yes, it is updated. If no, It is inserted. The SQL statement is as follows:

 
 

  
  
merge into tj_test1 tt1  
  
  
using tj_test tt  
  
  
on (tttt1.id=tt.id)  
  
  
when matched then  
  
  
update set  
  
  
tttt1.name=tt.name,  
  
  
tttt1.age=tt.age  
  
  
when not matched then  
  
  
insert values(  
  
  
tt.id,  
  
  
tt.name,  
  
  
tt.age)  
 
 

Query table tj_test1 (compare the data in the original table, update the field NAME in ID = 1 ROW, and add two more data)

 
 

  
  
select * from tj_test1 
 
 

Change the row data as follows:

1 jan 23 -- the original jkl value here is updated

3 joe 27 -- insert not in the original table

2 kk 22 -- insert not in the original table

Update if it exists and insert if it does not exist.

9i supports Merge, but only select subqueries are supported,

For a single data record, you can write select ...... From dual subquery.

Syntax:

 
 

  
  
MERGE INTO table  
  
  
USING data_source  
  
  
ON (condition)  
  
  
WHEN MATCHED THEN update_clause  
  
  
WHEN NOT MATCHED THEN insert_clause;  
  
  
 
 
 

For example:

 
 

  
  
MERGE INTO course c  
  
  
USING (SELECT course_name, period,  
  
  
course_hours  
  
  
FROM course_updates) cu  
  
  
ON (c.course_name = cu.course_name  
  
  
AND c.period = cu.period)  
  
  
WHEN MATCHED THEN  
  
  
UPDATE  
  
  
SET c.course_hours = cu.course_hours  
  
  
WHEN NOT MATCHED THEN  
  
  
INSERT (c.course_name, c.period,  
  
  
c.course_hours)  
  
  
VALUES (cu.course_name, cu.period,  
  
  
cu.course_hours);   
 
 

The above content is an introduction to Oracle merge into usage and examples. I hope you will get something better.