Usage of merge into in Oracle

Many times we need to filter the table to update, INSERT, delete and other operations. So if we're going to be a lot of trouble with a single table, here's how the merge into usage will greatly optimize the time and amount of code for our operation table.

For example, create a new 2 table first:

Create   table book   (  ID number  ,  name varchar (+), price number   ,  primary KEY ( ID)) Create   table  pbook   as  select  * from   bookdelete   Pbook

CREATE TABLE A as (select ...) here. Unfamiliar people can remember, can be used later, the equivalent of backing up a table, both the table structure and data. Insert different data separately, as follows

Now we operate on the table book (hereinafter referred to as Table A), we need to update the price of a table at the same time 0 of the data, insert the data not in table A, and through the conditions to filter out the price of the B table is greater than 1000 of the data of great data.

Merge into  book   a using   pbook B on (a.id=b.id) when  matched  then   update  set  a.price= b.price*0.8 where    a.price=0 delete   where  (b.price>1000) when isn't  matched  Then Insert  (A.id,a.name,a.price) values  (B.id,b.name,b.price);

Description: When the matched then is the condition, that is, if the table a a.price not equal to 0 will not go to execute the UPDATE statement, the subsequent delete where is also in the update filter some data. No, you can not add

The results of table A are as follows:

The statement did: 1, delete the id=1 data, 2, updated the 2,3 data. 3, inserted 4, 52 new data. 4, the data with ID 4 is unchanged

Merge into is a DML statement that needs to be rollback and commit to end things

Discovery: The test Insert data order is unordered (there is no rule, if someone found the law please advise).

Usage of merge into in Oracle