How to use merge into

1. Syntax

The syntax of merge into is as follows:

MERGE INTO [target-table] T USING [source-table sql] S ON([conditional expression] and [...]...)
WHEN MATCHED
THEN [UPDATE sql]
WHEN NOT MATCHED
THEN [INSERT sql]

Determine whether the source table S and target table T meet the conditions in ON. If it is satisfied, use the S table to update the T table. If it is not satisfied, insert the S table data into the T table. But there are many options, as follows:

• Normal mode
• Only update or insert
• Unconditional insert implementation
• update with delete

2. Test table

-- Target table
 create table target
 (
 id NUMBER not null,
 name VARCHAR2(12) not null,
 year NUMBER
 );

 -- Source table
 create table source
 (
 id NUMBER not null,
 aid NUMBER not null,
 name VARCHAR2(12) not null,
 year NUMBER,
 city ​​VARCHAR2(12)
 );
 -- Insert test data
 insert into target values(1,'liuwei',20);
 insert into target values(2,'zhangbin',21);
 insert into target values(3,'fuguo',20);

 insert into source values(1,2,'zhangbin',30,'Jilin');
 insert into source values(2,4,'yihe',33,'Heilongjiang');
 insert into source values(3,3,'fuguo','','Shandong');

The query results of the two tables are as follows:

SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20
          2 zhangbin 21
          3 fuguo 20

 SQL> select * from source;

         ID AID NAME YEAR CITY
 ---------- ---------- ------------ ---------- ------------
          1 2 zhangbin 30 Jilin
          2 4 yihe 33 Heilongjiang
          3 3 fuguo Shandong

3. Normal mode

Now we simply use merge into to to implement the function of performing update operations when the conditions in on are met, otherwise performing insert operations. Check the target and source tables, where the id and source and aid in the target table are associated. Now we want to implement the year of the target table when the id of the target table matches the aid of the source, use the year of the source table to update the year of the target table. Otherwise, insert the records in the source into the target table, and implement the specific SQL as follows:

merge into target t using source s on ( = )
when matched then
update set  = 
when not matched then
insert values(, , );

The execution results are as follows:

SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20 -- Original record
          2 zhangbin 30 -- Updated to 30
          3 fuguo -- Updated to empty
          4 yihe 33 -- New insert record

4. Only update

Restore the data of the target table, and now implement the operation of updating the year of the target table when the id of the target table matches the aid of the source table, and implement sql as follows:

merge into target t using (select aid, name, year from source) s on ( = )
when matched then
update set  = ;

The execution results are as follows:

SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20 -- Original record
          2 zhangbin 30 -- Updated to 30
          3 fuguo -- Updated to empty

To restore the data of the target table, we can also add limited conditions in the update clause. For example, when using the year of the source table to update the year of the target table, the records that limit the source table must be in Jilin, except for the conditions that match the target, the city must be in Jilin. The specific implementation of sql is as follows:

merge into target t using source s on ( = )
 When matched then
 update set = where = 'Jilin';

The execution results are as follows:

SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20 -- Original record
          2 zhangbin 30 -- Updated to 30
          3 fuguo 20 -- Original record

5. Onlyinsert

Restore the data of the target table, and now implement when the id of the target table does not match the aid of the source, insert the record in the source into the target table. The specific implementation of sql is as follows:

merge into target t using source s on ( = )
when not matched then
insert(, , ) values(, , );

The execution results are as follows:

SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20 -- Original record
          2 zhangbin 21 -- Original record
          3 fuguo 20 -- Original record
          4 yihe 33 -- Added new records

The insert clause can also add limited conditions, similar to update, which will not be described here.

6. Unconditional insert

Restores the data from the target table. Sometimes we need to insert all the data in a table into another table. At this time, we can add constant filtering predicates to achieve it, so that it only meets matches and mismatches, so that there is only update or insert. Here we want to insert all unconditionally, just set the on condition to permanent leave. Use the source table to update the target code as follows:

merge into target t using source s on(1 = 0) -- Set permanent false matching conditions
 When not matched then
 insert(, , ) values(, , );

The execution results are as follows:

SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20
          2 zhangbin 21
          3 fuguo 20
          2 zhangbin 30 -- Added new records
          4 yihe 33 -- Added new records
          3 fuguo -- Add new records

7. Update with delete

Restores the data from the target table. Deletes records in the target table that match the source table that matches the specified criteria. It should be noted that the delete clause is attached to the update clause, that is, to have the delete clause, the update clause must be included. The following statement uses records from the source table to match records in the update target table. At the same time, delete the records with id 2 in the target table in the matching record. The specific implementation of sql is as follows:

merge into target t using source s on( = )
when matched then update set  = 
delete where( = 2);

The execution results are as follows:

-- Records with id 2 are deleted
 SQL> select * from target;

         ID NAME YEAR
 ---------- ------------ ----------
          1 liuwei 20 -- Original record
          3 fuguo -- year updated