What is the usage of merge into in oracle

In Oracle, "merge into" is used to update data in a table. You can insert data from one table into another table. If the data is already in the inserted table, the data will be updated. If the data does not exist, new data will be added to the inserted table.

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

What is the usage of merge into in Oracle

Oracle9g introduced the MERGE command. You can perform inserts and update operations on a table at the same time in one SQL statement. The MERGE command selects from one or more Select rows in the data source to update or insert into one or more tables. In Oracle 10g, MERGE has the following improvements:

1, UPDATE or INSERT clause is optional

2 , UPDATE and INSERT clauses can be added with WHERE clause

3. Use constant filter predicate in ON condition to insert all rows into the target table, without connecting the source table and target table

4. The UPDATE clause can be followed by a DELETE clause to remove some unnecessary rows

5. The source table is the table followed by the using keyword, and the target table is the table that will be merged into

6. All updates, inserts, and deletes in merge into are performed on the target table. Since the merge into operation table has been formulated, update, insert, and delete do not need to display the table name

7. In short, the function of merge into is to solve the problem of using table B and new table A data. If If it does not exist in table A, insert the data from table B into table A or insert data into a table. If the table already has the data, it will be updated. Otherwise, new data will be added.
Syntax:

MERGE INTO [your table-name] [rename your table here] USING ( [write your query here] )[rename your query-sql and using just like a table] ON ([conditional expression here] AND [...]...) WHEN MATHED THEN [here you can execute some update sql or something else ] WHEN NOT MATHED THEN [execute something else here ! ]

Next, let’s test it directly:

Requirement 1: Insert a piece of data into a table. If the data already exists in the table, update it, otherwise create a new one. Add

First create a table TEST_ONE

create table TEST_ONE( ID NUMBER not null primary key, NAME VARCHAR2(255), IP VARCHAR2(255), MEMO VARCHAR2(255))commit;

Add a few pieces of data as test data

INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (1, '2', '3', '周文军'); INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (2, '66', '366', '2656'); INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (3, '5656', '626', '2626'); INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (4, '5656', '2626', '626');

Okay, our data table has been built, as shown below:

If we need to add a new piece of data, we usually do it like this

INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (5, 'mrhu', '127.0.0.1.0', '王先生的IP');

But we hope to use the ID to judge first. If the data is not added and the data is updated, how to achieve it?

Then the merge into command comes, just type the code:

merge into TEST_ONE mtb using (select '5' as id, 'mrhu' as name,'127.0.0.1.0' as ip,'王先生的IP' as memo from dual)mmb on (mtb.id = mmb.id)when matched THENupdate set mtb.name = mmb.name,mtb.ip = mmb.ip,mtb.memo=mmb.memo when not matched theninsert (mtb.id, mtb.name,mtb.ip,mtb.memo) VALUES(mmb.id,mmb.name,mmb.ip,mmb.memo);

Run as follows:

Let’s take a look at the data in the table:

Data added successfully!

So how do we test updates? It's very simple. We change memo='Mr. Wang's IP' to memo='This beautiful girl's IP' for testing.

merge into TEST_ONE mtb using (select '5' as id, 'mrhu' as name,'127.0.0.1.0' as ip,'本大美女的IP' as memo from dual)mmb on (mtb.id = mmb.id)when matched THENupdate set mtb.name = mmb.name,mtb.ip = mmb.ip,mtb.memo=mmb.memo when not matched theninsert (mtb.id, mtb.name,mtb.ip,mtb.memo) VALUES(mmb.id,mmb.name,mmb.ip,mmb.memo);

Run as follows:

Let's take a look Data in the table:

The data has been updated successfully!

Requirement 2: Add the data in table A to table B, and it is required to judge by the primary key. If the data is included, update it, otherwise add new one

We create another table TEST_TWO As table B, TEST_ONE is used as table A

create table tes( ID NUMBER not null primary key, CODE VARCHAR2(255), MEMO VARCHAR2(255));commit;

Okay, table TEST_TWO is created, let’s add a piece of data first!

INSERT INTO ROOT.TEST_TWO (ID, CODE, MEMO) VALUES (5, 'mrhu', '隔壁老王的IP');

Let’s take a look at the data in TEST_TWO:

Let’s import the data in TEST_ONE into our newly created table. Through analysis, we found that the TEST_TWO table has There is a piece of data with ID 5. There is also a piece of data with ID 5 in TEST_ONE. The expected execution effect is that the memo field value of the data with TEST_TWOID 5 will be updated to 'the IP of this beautiful woman' in TEST_ONE, and other values will be added. operate.

Let’s write code to verify:

merge into TEST_TWO mtb using (select id,name,ip,memo from TEST_ONE) mmb on (mtb.id = mmb.id)when matched THENUPDATE set mtb.code = mmb.name,mtb.memo = mmb.memo when not matched THENinsert (mtb.id,mtb.code,mtb.memo) values (mmb.id,mmb.name,mmb.memo);

Let’s take a look at the effect:

The execution result is consistent with the expected result. Okay, how to use merge into? Have you learned it? If you like it, please follow and support it!

Recommended tutorial: "Oracle Video Tutorial"

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