Shulou(Shulou.com)05/31 Report--

I believe many inexperienced people don't know what to do about how to use merge into in oracle. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The form of merge into:

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

Function: determine whether Table B and Table A meet the conditions in ON. If so, use Table B to update Table A. if not, insert Table B data into Table A, but there are many options, as follows:

1. Normal mode

two。 Only update or only insert

3. Conditional update or conditional insert

4. Full plug-in insert implementation

5. Update with delete (I think it can be implemented with 3)

Let's test them one by one.

The test builds the following table:

Create table A_MERGE (id NUMBER not null, name VARCHAR2 (12) not null, year NUMBER); create table B_MERGE (id NUMBER not null, aid NUMBER not null, name VARCHAR2 (12) not null, year NUMBER, city VARCHAR2 (12)); create table C_MERGE (id NUMBER not null, name VARCHAR2 (12) not null, city VARCHAR2 (12) not null); commit

The screenshot of the table structure is shown below:

The structure of the A _ merge table:

B _ memory table structure

C _ memory table structure

1. Normal mode

First insert test data into A_MERGE and B_MERGE:

Insert into A_MERGE values (1); insert into A_MERGE values (2); insert into A_MERGE values (3); commit; insert into B_MERGE values (1); insert into B_MERGE values (2); insert into B_MERGE values (3);

At this point, the screenshots of the data in the A_MERGE and B_ memory tables are as follows:

A _ merge table data:

B _ merge table data:

Then use merge into to update the data in A_MERGE with B_MERGE:

MERGE INTO A_MERGE A USING (select B. name from B_MERGE B. year from B_MERGE B) C ON (A.id=C.AID) WHEN MATCHED THEN UPDATE SET A.YEAR=C.YEAR WHEN NOT MATCHED THEN INSERT (A.IDMague A.NAMEMagna A.YEAR) VALUES (C.AIDMagery C. name C.YEAR); commit

At this point, the screenshot of the table data in A_MERGE is as follows:

two。 Update mode only

First insert two pieces of data into B_MERGE to show that only update does not have insert, and there must be one data that already exists in A.

If another data does not exist in A, the insert data statement is as follows:

Insert into B_MERGE values (4 and 1); insert into B_MERGE values (5 and 5); commit (23)

At this point, the screenshots of the data in the A_MERGE and B_ memory tables are as follows:

Screenshot of the data in the A _ merge table:

Screenshot of the data in the B _ merge table:

Then update the A_MERGE with B_MERGE again, but only update, without writing the insert section.

Merge into A_MERGE A USING (select B. AID from B_MERGE B. name, B.YEAR from B_MERGE B) C ON (A.ID=C.AID) WHEN MATCHED THEN UPDATE SET A.YEARCHYEARC.YEAR; commit

After merge, the screenshot of the data from the Amerge table is as follows: it can be found that only the age of AID=1 has been updated, and no data of AID=4 has been inserted.

3. Insert mode only

First, change one of the data in B_MERGE, because the new data you added when you last tested update was not inserted into A_MERGE, and you can use it this time.

Update B_MERGE set year=70 where AID=2; commit

The screenshot of the table data of A_MERGE and B_MERGE is as follows:

A _ merge table data:

B _ merge table data:

Then use B_MERGE to update the data in A_MERGE. At this time, only insert is written, not update:

Merge into A_MERGE A USING (select B. name from B_MERGE B. year from B_MERGE B) C ON (A.ID=C.AID) WHEN NOT MATCHED THEN insert (A.IDMague A.NAMEMagna A.YEAR) VALUES (C.AIDMagery C. name C.YEAR); commit

At this point, the screenshot of the table data of A_MERGE is as follows:

4. Insert and update with where condition.

After we have done the condition matching in on, we can also make a conditional judgment on the records filtered by on in the later insert and update to control which to update and which to insert.

The sql code for the test data is as follows. We modified two names and added two personnel information in B_MERGE, but they come from different provinces.

So we can control which can be modified and which can be inserted by adding provincial conditions:

Update B_MERGE set name='yihe++' where id=2; update B_MERGE set name='liuwei++' where id=4; insert into B_MERGE values (6 and 6); insert into B_MERGE values (7 and 7); commit (24).

The screenshot of the data in the A _ MGERGE table is as follows:

B _ merge table data:

Then use B_MERGE to update A_MERGE, but add conditional restrictions after insert and update to control the update and insertion of data:

Merge into A_MERGE A USING (select B.AID where B. yearbook B.city from B_MERGE B) C ON (A.id=C.AID) when matched then update SET A.name=C.name where C.city! = 'Jiangxi' when not matched then insert (A.IDDiao A.namegamemeA.year) values (c.AIDMagery C.nameMenC.year) where C. citybook 'Jiangxi'; commit

The screenshot of A_MERGE is as follows:

5. Unconditional insert.

Sometimes we need to insert all the data in one table into another table, and we can add constant filter predicates to satisfy only matches and mismatches, so that there is only update or only insert. Here, if we want to insert it all unconditionally, we just need to set the condition in on to permanent leave. Update the C_MERGE code with B_MERGE as follows:

Merge into C_MERGE C USING (select B. AID from B_MERGE B. name from B_MERGE B) C ON (1x 0) when not matched then insert (C.IDD C.Name.City) values (B.AIDMagne B.City); commit

The data screenshot of the C _ merge table before merge is as follows:

Screenshots of B_MERGE data are as follows:

The data screenshot of the C _ merge table after merge is as follows:

6. Update with delete

MERGE provides the option to clear rows when performing data operations. You can include the DELETE clause in the WHEN MATCHED THEN UPDATE clause.

The DELETE clause must have a WHERE condition to delete rows that match certain conditions. Rows that match the DELETE WHERE condition but not the ON condition are not deleted from the table.

But I think this update with where condition is similar, it is all control update, and it can be realized by update with where condition.

After reading the above, have you mastered how to use merge into in oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.