The new features of MERGE (UPDATE WHERE, DELETE WHERE, INSERT WHERE)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The MERGE statement is Oracle9i new syntax, used to merge UPDATE and INSERT statements.
With the MERGE statement, according to a table or query sub connected to another table query, connection condition of UPDATE, unable to match the execution of INSERT. This grammar only need a full table scan to finish all the work, the execution efficiency is higher than that of INSERT+UPDATE.
Grammar
*************************************************************
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause
**************************************************************
MERGE Oracle 10g has the following improvements:
1, The UPDATE or INSERT clause is optional
2, The UPDATE and INSERT clauses can be added WHERE clause
3, ON condition with constant filter predicates to insert all the lines in the target table, do not need to connect the source and target tables
4, With the DELETE clause to remove some unwanted UPDATE clause.

Said here on my own understanding:
The first point is available in the MERGE INTO WHEN MATCHED THEN grammar only write or write only WHEN NOT MATCHED THEN. Here no longer.
The second point is coupled with WHERE behind THEN after UPDATE or INSERT statement we can, for example, such demand: when the A table in the ID does not exist in the B table for insert. When present, the A table is updated when the NAME is not empty. This situation requires + UPDATE after WHERE in words;.
The third point mean we can not associated target table and the USING table in the ON condition, can be written as 1=1 such conditions in the ON condition. For example, we have such a demand: when the isAllAdd parameter is false, normal MERGE INTO. When the isAllAdd parameter is true, all performing insert. We can use this new feature, are selected so as to achieve a SQL can meet the business purpose for the condition in the ON.
Fourth we more than can be inserted in the MERGE update in INTO, but also on the matching data to delete operation. This point is very important, personally think that fourth points is a point in the improved most in need of our learning. For example, we have such needs: A table with school ID, examination, examination results, the year the final exam four fields. We give the final exam do batch delete function, when there is no result of the mid-term examination, delete the data. When the result of the mid-term examination, the examination results at the end of space. The traditional writing will need to delete the personnel information in the List database query, see each of which is a modified or deleted in execution, respectively called. We need to just now
MERGE INTO A
USING (TABLE EMPLOYEE) B
ON (A.EMPLOYEEID = B.EMPLOYEEID AND A.YEAR = B.YEAR)
WHEN MATCHED THEN
Test UPDATE SET period = ""
DELETE WHERE IS NULL final exam

Special instructions:
DELETE words written in MATCHED, was unable to delete the mismatch errors.
When the DELETE with the UPDATE sentence after sentence is, DELETE filter for UPDATE words modified data. Such as the need to remove all the C field =" 1" data, UPDATE words C fields for all the data are updated to 1, it will delete all the data, rather than the original 1 data.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Dempsey at December 09, 2013 - 10:46 AM