MERGE/ USING - A beneficial SQL Statement

Often times in our JDA commands, we are faced with doing an INSERT, UPDATE or DELETE based on data in a table as it relates to another.  For example, I am loading Inventory Data into a transaction table to be posted for polling.  I possibly have the following scenarios:

• If my Inventory record exists in the destination table, then update it
• If the record does not exist, then insert it
• Possibly, if the record exists, but my inventory is in a different status (depends on specific use case), then delete it from the destination

To implement this logic, a MOCA Command would be something like this (pseudo code):

[select data from destination table where pk=@lodnum] catch(-1403)

|

if (@?=0)

  if (@data has wrong status)

    perform my delete of destination table

  else

    perform my update of destination table

else

  perform my insert of the destination table

This in itself is not difficult to follow from coding perspective.  However, we do have three different sets of code branches to manage and maintain.  The MERGE/USING statement (both SQL and Oracle support it with slight differences in usage and syntax) allows for all three possibilities to be encompassed in one statement.

The pseudo syntax on MERGE/USING is as follows.  The statement was first introduced in SQL 2008 and Oracle 9i. For specific syntax and usage as applicable to your needs and database, please consult the relevant Database Documentation.

MERGE <destination table> 

USING <source table> /* Can be a select */

ON ( key columns )

WHEN MATCHED <and extra clauses>

  THEN UPDATE....

WHEN NOT MATCHED <BY TARGET or SOURCE>

  THEN INSERT....

You only have options of MATCHED and NOT MATCHED with extra qualifiers to implement the possibility of a DELETE statement.  Also note that UPDATE and DELETE statements can be provided at the same time under the MATCHED clause.

The benefit is that using one SQL statement, all three DML operations can be performed.  From performance standpoint, we have one cursor open that is traversing the source table and doing operations in the destination table in one go.  Rather than doing multiple open/ close cursors inside a loop.

Please note that MERGE statement is optimized for merging a SET of data and not for Single Row operations.  In a multi-row SET, the performance gain of a MERGE/USING statement can be almost 15 times, that of doing the same operations as INSERT/UPDATE.  So it is best to use it when you have a need of a larger set of data to be merged with another table.  Also note that the entire MERGE operation is one DML statement and hence one COMMIT context.

Hope you can find a use case appropriate for this statement.