Advantage Developer Zone

 
 
 

The SQL MERGE Statement

Tuesday, April 08, 2008

Advantage 9.0 adds support for an SQL MERGE statement which can be used to combine the data from two tables together. It can also be used to perform an “UPSERT” into a table allowing for updating or inserting the record with a single statement.

MERGE Statement

The MERGE Statement consists of specifying the tables, defining the match condition and specifying UPDATE and INSERT statements. The syntax is specified below:

MERGE [INTO] <tableref>
[USING <tableref>]
ON <search-condition>
<WHEN MATCHED THEN <update specification>> |
<WHEN NOT MATCHED THEN <insert specification>>

The search-condition is any valid logical expression. The update specification is an UPDATE statement which will update records when a match is found. The insert specification is an INSERT statement which can be provided to insert records when no match is found.

Combining Data Using MERGE

To combine the data from two tables you must specify the two tables and they must have a primary key which could match. For instance, a doctor’s office partners with another office which uses the same software. The same patient may have been seen by both doctors so importing the patients from one office to the other may fail. The MERGE statement will update matching patient information and insert new patients.

The example statement below will add or update the information from NewPatient into the Patient table using the PatientID field as the primary key. The LastName and FirstName fields will be updated when the PatientID matches. If the PatientID is not found a new record will be inserted into the Patient table.

MERGE Patient p1 USING NewPatient p2
ON ( p1.PatientID = p2.PatientID)
WHEN MATCHED THEN
UPDATE SET p1.LastName = p2.LastName, p1.FirstName = p2.FirstName
WHEN NOT MATCHED THEN
INSERT VALUES (p2.PatientID, p2.LastName, p2.FirstName)

Using MERGE for an UPSERT


An “UPSERT” is an update or insert type statement. This allows for inserting a new record into the table if it is not found and updating the record if it already exists. Without the MERGE statement this would have to be handled in an SQL script. Compare the two statements below.

TRY
INSERT INTO Table VALUES (10, ‘Franz’, ‘Chris’);
CATCH ADS_SCRIPT_EXCEPTION
IF __errcode = 5097 THEN -- Primary Key exception
-- Record already exists so perform an update
UPDATE Table SET LastName = ‘Franz’, FirstName = ‘Chris’ WHERE ID = 10;
ELSE
-- some other error occurred so raise the error
RAISE;
END IF;
END TRY;

MERGE Table ON (ID = 10)
WHEN MATCHED THEN
UPDATE Table SET LastName = ‘Franz’, FirstName = ‘Chris’
WHEN NOT MATCHED THEN
INSERT VALUES (10, ‘Franz’, ‘Chris’)

Replication and MERGE

Advantage 9.0 added an option to use MERGE statements for replication. When this option is used for Updates a record that is not found at the subscriber will be inserted instead of returning an error. Likewise using the merge option for inserts will perform an update at the subscriber instead of returning an error for a record which already exists.

Summary

The MERGE statement simplifies combining data from similar tables and provides functionality for more flexible insertion and updating of records. MERGE can be used in replication which can reduce errors.