Simple use of merge commands in Oracle

Simple use of merge commands in Oracle

Merge command

With this merge, You can execute the inserts and updates operations on a table in an SQL statement at the same time.

You can use the meger statement to merge two specified tables. The syntax is as follows:
Meger into table1_name
USING table2_name ON join_condition
When matcheo then update set...
When not matched then insert... VALUES...
Syntax description:
Table1_name indicates the target table to be merged.
Table2_name indicates the source table to be merged.
Join_condition indicates the merging condition.
When matcheo then update indicates that if the merging conditions are met, the update operation is performed.
When not matched then insert indicates that if the conditions are not met, the insert operation is performed.

Update and insert
If you only want to merge the Qualified Data in the source table to the target table, you can only use the update clause. If you want to merge the data that does not meet the conditions in the source table to the target table, you can only use the insert clause.
In the update clause and insert clause, you can use the where clause to specify the conditions that have been inserted. In this case, two layers of filtering conditions are provided for the merge operation. The first layer is the merging condition specified by the on clause in the meger clause, and the second layer is the where condition specified in the update or insert clause. This makes the merge operation more flexible and refined.
Here we create two tables, one for the person table and the other for the newpersono table. The two tables have the same structure.
SQL> create table person (
2 pid number (4 ),
3 page number (3)
4 );
The table has been created.
-- Insert three rows of data
SQL> insert into person values (1, 20 );
One row has been created.
SQL> insert into person values (2, 21 );
One row has been created.
SQL> insert into person values (3, 22 );
One row has been created.
SQL> create table newperson (
2 pid number (4 ),
3 page number (3)
4 );
The table has been created.
-- Insert three rows of data
SQL> insert into newperson values (1,100 );
One row has been created.
SQL> insert into newperson values (4,100 );
One row has been created.
SQL> insert into newperson values (5,100 );
One row has been created.
SQL> select * from person;

PID PAGE
--------------------
1 20
2 21
3 22
SQL> select * from newperson;
PID PAGE
--------------------
1 100
4 100
5 100
SQL> merge into person p1
2 using newperson p2
3 on (p1.pid = p2.pid)
4 when matched then
5 update set p1.page = p2.page
6 when not matched then
7 insert (pid, page) values (p2.pid, p2.page );

The three rows have been merged.
-- The preceding SQL statement sets the corresponding page in person to the age in newperson when the pid in person is equal to the pid in newperson, insert non-conforming data to person. The execution result is as follows:
SQL> select * from person;
PID PAGE
--------------------
1 100
2 21
3 22
5 100
4 100
-- The data in the newperson table will not change:
SQL> select * from newperson;
PID PAGE
--------------------
1 100
4 100
5 100