Oracle Foundation Merge into

The merge into statement is a new syntax for merging update and INSERT statements in oracle9i.

Through the merge statement, the other table is queried based on the join criteria of a single table or multi-table union query, and an update on the join condition match does not match the execution insert.

This syntax only needs a full table scan to complete the whole work, the execution efficiency is higher than insert+update. With this merge you can perform both insert and update operations on a table in one SQL statement. The merge has some new features in Oracle 10g, and I'll cover these new features later. First look at the merge syntax as follows:

MERGE intoTest_new DMUSING(SELECT Date_cd, HR_CD, Date_hr, DECODE (groupin               G (city_id), 1, 9999, city_id) as city_id, DECODE (GROUPING (system_id), 1, -9999, system_id) as system_id, SUM (gsm_reg_usercnt) as gsm_reg_usercnt, sum (td_reg_usercnt) as td_reg_usercnt, sum (Td_reg_userrat)  As Td_reg_userrat, sum (gsm_poweron_usercnt) as gsm_poweron_usercnt, sum (td_poweron_usercnt) as TD_POWERON_USERCNT, SUM (Td_poweron_userrat) as Td_poweron_userrat from Test_old GROUP by D ATE_HR, DATE_CD, Hr_cd, ROLLUP (system_id), ROLLUP (city_id)) TMP on(DM. DATE_CD = TMP. DATE_CD and dm.hr_cd = Tmp.hr_cd and DM. city_id = TMP. CITY_ID and DM. system_id = TMP. SYSTEM_ID)When matched then UPDATE SETDm. gsm_reg_usercnt = TMP. GSM_REG_USERCNT, DM. td_reg_usercnt = TMP. TD_REG_USERCNT, DM. Td_reg_userrat = TMP. Td_reg_userrat, DM. gsm_poweron_usercnt = TMP. GSM_POWERON_USERCNT, DM. td_poweron_usercnt = TMP. TD_POWERON_USERCNT, DM. Td_poweron_userrat = TMP. Td_poweron_userrat, DM. DATE_HR = TMP. Date_hrWhen isn't matched then INSERT(DM. DATE_CD, DM.HR_CD, DM. DATE_HR, DM. city_id, DM. system_id, DM. GSM_REG_USERCNT, DM. TD_REG_USERCNT, DM. Td_reg_userrat, DM. GSM_POWERON_USERCNT, DM. TD_POWERON_USERCNT, DM. Td_poweron_userrat) VALUES (TMP. DATE_CD, TMP.HR_CD, TMP. DATE_HR, TMP. city_id, TMP. system_id, TMP. GSM_REG_USERCNT, TMP. TD_REG_USERCNT, TMP. Td_reg_userrat, TMP. GSM_POWERON_USERCNT, TMP. TD_POWERON_USERCNT, TMP. Td_poweron_userrat
）；

　　When the matched then UPDATE SET indicates that the keyword matches on, the modification is done.

However, it is important to note that you cannot modify the value of the ON keyword when you make a modification.

　　When the matched then INSERT indicates that a new action is made when the keyword on the on is not matched, that is, when no such record exists in the Test_new table.

At this point, to do the new operation, you can set the value of the field on the inside.

In Oracle 10i, the merge has some of the following new features.

1. Update or INSERT clauses are optional If a system, there is an order form, now requires the record of new orders to be reflected in the Order History table Order_history, we can write the following script:

MERGE into Order_history H USING (SELECT order_id,--order number customer_id,--customer number EMPLOYEE_ID,--Employee Number order_date,--order date; Required_date,-                -Expected Arrival date Shipped_date,--date of shipment SHIPPER,--Shippers FREIGHT              ,--Freight Ship_nam,--shipper name; ship_address,--Shipper Address ship_city             ,--Shipper's city; ship_region,--Shipper's area; ship_postalcode,--Shipper ZIP Code            Ship_country – the country from which the shipper is located from Order_dtl where To_char (oder_date, ' yyyy-mm-dd ') = ' 20110530 ') oon ( o.order_id = h.order_id) When isn't matched then INSERT (h.order_id, H.cust omer_id, h.employee_id, H.order_date, H.required_         DATE,    H.shipped_date, H.shipper, H.freight, H . Ship_nam, H.ship_address, h.ship_city, H.ship_                       Region, H.ship_postalcode, H.ship_country) VALUES (            o.order_id, o.customer_id, o.employee_id, O.order_date, O.required_date, O.shipped_date, O.shi Pper, O.freight, O.ship_nam, O.ship_add RESS, O.ship_city, O.ship_region, O.ship_postalco DE, O.ship_country
）；

　　

As can be seen from the above, matched or not matched is optional. Not necessarily

When isn't matched then UPDATE SET ..... When matched and then INSERT 2. Update and INSERT clauses can be added to the WHERE clause

Now, as demand changes, we just need to synchronize employee 1001 's order data to the Order History table.

MERGE into Order_history H USING (SELECT order_id,--order number customer_id,--customer number EMPLOYEE_ID,--Employee Number order_date,--order date; Required_date,-                -Expected Arrival date Shipped_date,--date of shipment SHIPPER,--Shippers FREIGHT              ,--Freight Ship_nam,--shipper name; ship_address,--Shipper Address ship_city             ,--Shipper's city; ship_region,--Shipper's area; ship_postalcode,--Shipper ZIP Code     Ship_country – Shipper's country from ORDER_DTL) oon (o.order_id = h.order_id) while matched then UPDATE     SET h.customer_id = o.customer_id, h.employee_id =     o.employee_id, h.order_date = o.order_date, h.required_date = O.required_date,             H.shipped_date = o.shipped_date, H.shipper = O.shipper          , H.freight = o.freight, H.ship_nam = O.ship_nam         , h.ship_address = o.ship_address, h.ship_city = o.ship_city , h.ship_region = o.ship_region, H.ship_postalcode = O.ship_postal CODE, h.ship_country = o.ship_country WHERE o.employee_id = ' 1001 ' When not matched T            HEN INSERT (h.order_id, h.customer_id, h.employee_id           , H.order_date, H.required_date, h.shipped_date             , H.shipper, H.freight, H.ship_nam, H.ship_address          , H.ship_city, H.ship_region, H.ship_postalcode , H.ship_country) VALUES (o.order_id, O.custom er_id, o.employee_id, O.order_date, O.required_da                 TE, O.shipped_date, O.shipper, O.freight               , O.ship_nam, O.ship_address, o.ship_city   , O.ship_region, O.ship_postalcode, O.ship_country) WHERE o.employee_id = ' 1001 ';

　　

Oracle Foundation Merge into