What is the usage of merge in oracle

In Oracle, the merge command can perform inserts and updates operations on a table at the same time in one SQL statement. The merge statement is often used to perform merge operations on two specified tables. The syntax is "MEGER INTO merge." Target table USING merge source table ON condition".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of merge in oracle

merge command

Through this merge you can perform inserts and updates on a table in one SQL statement at the same time

Using the meger statement, you can perform a merge operation on the 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...

The syntax description is as follows:

table1_name indicates the target table that needs to be merged.

table2_name indicates the source table that needs to be merged.

join_condition represents the merge condition.

when matcheo then update means that if the conditions for merging are met, the update operation will be performed.

when not matched then insert means that if the conditions are not met, the insertion operation will be performed.

update and insert

If you only want to merge the data that meets the conditions in the source table into the target table, you can only use the update clause. If you want to merge the data in the source table that does not meet the merge conditions. To merge data into the target table, you can only use the insert clause.

In both the update clause and the insert clause, you can use the where clause to specify the conditions for updated inserts. At this time, for the merge operation, two levels of filtering conditions are provided. The first level is the merge condition, specified by the on clause in the meger clause, and the second level is the where condition specified in the update or insert clause. This makes the merge operation more flexible and sophisticated.

Here we create two tables, one is the person table and the other is the newpersono table. The structures of the two tables are the same

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);

1 row has been created.

SQL> insert into person values(2,21);

1 row has been created.

SQL> insert into person values(3,22);

1 row has been created.

SQL> create table newperson(
2  pid number(4),
3  page number(3)
4  );

Table has been created.

--插入三行数据
SQL> insert into newperson values(1,100);

1 row has been created.

SQL> insert into newperson values(4,100);

1 row has been created.

SQL> insert into newperson values(5,100);

1 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);

3 rows have been merged.

--The above SQL statement is when the pid in person is equal to the pid in newperson, set the corresponding page in person to the age in newperson. When it does not match, insert the unqualified condition into person. The data. The execution results are as follows:

SQL> select * from person;
PID       PAGE
---------- ----------
1        100
2         21
3         22
5        100
4        100
--newperson表中的数据不会改变：
SQL> select * from newperson;
PID       PAGE
---------- ----------
1        100
4        100
5        100

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is the usage of merge in oracle. For more information, please follow other related articles on the PHP Chinese website!