Simple and practical 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 202 213 22SQL> select * from newperson;PID PAGE---------- ----------1 1004 1005 100SQL> merge into person p12 using newperson p23 on (p1.pid=p2.pid)4 when matched then5 update set p1.page=p2.page6 when not matched then7 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 1002 213 225 1004 100 -- data in the newperson table is not changed: SQL> select * from newperson; PID PAGE ---------- -------- 1 1004 1005 100
