Db2 for i MERGE Statement: INSERT, UPDATE & UPSERT

The Db2 for i MERGE statement lets you compare a source data set with a target table and decide what to do when rows match—or do not match. In its most common form, MERGE updates existing rows and inserts new ones in a single SQL statement. This pattern is often called an upsert.

On IBM i, MERGE is especially useful for synchronizing staging tables, importing changed customer or product records, processing interface files, and replacing fragile “UPDATE first, then INSERT if not found” logic.

What does MERGE do in Db2 for i?

A MERGE statement brings four pieces together:

• Target: the table or view you want to change.
• Source: a table, view, common table expression, query, or row of values supplying the incoming data.
• Match condition: the rule that identifies the same business record in both data sets.
• Actions: the UPDATE, INSERT, or DELETE operation to perform for each condition.

The core pattern looks like this:

MERGE INTO target_table AS T
USING source_table AS S
   ON T.key_column = S.key_column
WHEN MATCHED THEN
   UPDATE SET target_column = source_column
WHEN NOT MATCHED THEN
   INSERT (key_column, target_column)
   VALUES (S.key_column, S.source_column);

This is one statement, but it does not mean every target row is changed. The match condition and optional search conditions determine the action for each source row.

Practical Db2 for i MERGE example

Assume an IBM i application keeps current customer information in APPDATA.CUSTOMER_MASTER. A daily feed is loaded into APPDATA.CUSTOMER_STAGE. Both tables use CUSTOMER_ID as the business key.

MERGE INTO APPDATA.CUSTOMER_MASTER AS T
USING APPDATA.CUSTOMER_STAGE AS S
   ON T.CUSTOMER_ID = S.CUSTOMER_ID

WHEN MATCHED THEN
   UPDATE SET
      T.CUSTOMER_NAME = S.CUSTOMER_NAME,
      T.EMAIL_ADDRESS = S.EMAIL_ADDRESS,
      T.STATUS         = S.STATUS,
      T.UPDATED_TS     = CURRENT_TIMESTAMP

WHEN NOT MATCHED THEN
   INSERT (
      CUSTOMER_ID,
      CUSTOMER_NAME,
      EMAIL_ADDRESS,
      STATUS,
      CREATED_TS,
      UPDATED_TS
   )
   VALUES (
      S.CUSTOMER_ID,
      S.CUSTOMER_NAME,
      S.EMAIL_ADDRESS,
      S.STATUS,
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
   );

For each row in the staging table, Db2 for i follows this decision:

1. Compare S.CUSTOMER_ID with T.CUSTOMER_ID.
2. If a target row exists, update its descriptive columns and timestamp.
3. If no target row exists, insert a new customer.

Rows that exist only in CUSTOMER_MASTER are not automatically deleted or expired. That distinction matters when you design synchronization jobs.

MERGE with additional WHEN MATCHED conditions

You do not have to update every matching row. Add an AND condition when an update should occur only if the incoming record is newer or materially different.

MERGE INTO APPDATA.CUSTOMER_MASTER AS T
USING APPDATA.CUSTOMER_STAGE AS S
   ON T.CUSTOMER_ID = S.CUSTOMER_ID

WHEN MATCHED
 AND S.SOURCE_CHANGE_TS > T.SOURCE_CHANGE_TS THEN
   UPDATE SET
      T.CUSTOMER_NAME   = S.CUSTOMER_NAME,
      T.EMAIL_ADDRESS   = S.EMAIL_ADDRESS,
      T.STATUS           = S.STATUS,
      T.SOURCE_CHANGE_TS = S.SOURCE_CHANGE_TS,
      T.UPDATED_TS       = CURRENT_TIMESTAMP

WHEN NOT MATCHED THEN
   INSERT (
      CUSTOMER_ID,
      CUSTOMER_NAME,
      EMAIL_ADDRESS,
      STATUS,
      SOURCE_CHANGE_TS,
      CREATED_TS,
      UPDATED_TS
   )
   VALUES (
      S.CUSTOMER_ID,
      S.CUSTOMER_NAME,
      S.EMAIL_ADDRESS,
      S.STATUS,
      S.SOURCE_CHANGE_TS,
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
   );

This pattern protects a target row from being overwritten by an older interface record. It also reduces unnecessary updates, which can lower journal activity, trigger executions, and downstream change-data-capture traffic.

Using a query or CTE as the MERGE source

The source does not have to be a physical staging table. It can be a derived table that filters, cleans, or summarizes the incoming data first.

MERGE INTO APPDATA.PRODUCT_MASTER AS T
USING (
   SELECT PRODUCT_ID,
          TRIM(PRODUCT_NAME) AS PRODUCT_NAME,
          DECIMAL(PRICE, 11, 2) AS PRICE
     FROM APPDATA.PRODUCT_STAGE
    WHERE LOAD_STATUS = 'READY'
) AS S
   ON T.PRODUCT_ID = S.PRODUCT_ID

WHEN MATCHED THEN
   UPDATE SET
      T.PRODUCT_NAME = S.PRODUCT_NAME,
      T.PRICE        = S.PRICE,
      T.UPDATED_TS   = CURRENT_TIMESTAMP

WHEN NOT MATCHED THEN
   INSERT (PRODUCT_ID, PRODUCT_NAME, PRICE, UPDATED_TS)
   VALUES (S.PRODUCT_ID, S.PRODUCT_NAME, S.PRICE,
           CURRENT_TIMESTAMP);

This technique keeps transformation logic close to the data operation. For more complex preparation, a common table expression can make the source easier to test and maintain. See the related guide to Db2 for i CTEs and the WITH clause.

Single-row UPSERT with VALUES

A service program, stored procedure, or embedded SQL routine may need to upsert one row rather than merge an entire table. A VALUES expression can provide that source row:

MERGE INTO APPDATA.INVENTORY AS T
USING (
   VALUES (10025, 'MAIN', 48, CURRENT_TIMESTAMP)
) AS S (ITEM_ID, LOCATION_ID, QTY_ON_HAND, CHANGE_TS)
   ON T.ITEM_ID     = S.ITEM_ID
  AND T.LOCATION_ID = S.LOCATION_ID

WHEN MATCHED THEN
   UPDATE SET
      T.QTY_ON_HAND = S.QTY_ON_HAND,
      T.CHANGE_TS   = S.CHANGE_TS

WHEN NOT MATCHED THEN
   INSERT (ITEM_ID, LOCATION_ID, QTY_ON_HAND, CHANGE_TS)
   VALUES (S.ITEM_ID, S.LOCATION_ID, S.QTY_ON_HAND,
           S.CHANGE_TS);

In embedded SQL, replace the literals with host variables and cast them when Db2 cannot infer a precise type. A stored procedure can use the same pattern with input parameters; this works naturally with the techniques in our Db2 for i stored procedures guide.

Matching on composite keys

Many IBM i tables do not have a single-column key. Inventory might be unique by item and location; an order line may be unique by company, order number, and line number. Include every column that defines the business key:

ON T.COMPANY_NO = S.COMPANY_NO
AND T.ORDER_NO   = S.ORDER_NO
AND T.LINE_NO    = S.LINE_NO

The target should normally have a unique constraint or unique index on that same key. It documents the rule, prevents duplicate target rows, and gives the optimizer an efficient access path.

Common MERGE mistakes on IBM i

1. Duplicate keys in the source

If two source rows identify the same target row, Db2 cannot safely apply multiple conflicting actions to that target row in one MERGE. Clean or deduplicate the source before merging. A useful preparation pattern is ROW_NUMBER() over the business key, retaining only the newest row. The Db2 for i window functions tutorial shows how.

2. Using descriptive columns as the match key

Names, descriptions, email addresses, and status values can change. Match on the stable business key, then update the descriptive attributes.

3. Assuming target-only rows are handled

The standard upsert pattern is driven by source rows. A target row that has no corresponding source row is normally left alone. If the business requirement is “expire everything missing from today’s complete feed,” handle that explicitly with a controlled follow-up statement or a carefully designed delete branch supported by your IBM i release and tested against your data rules.

4. Updating columns used by the ON condition

Avoid changing the key columns that determine the match. Treat the ON clause as record identity and the UPDATE SET list as mutable attributes.

5. Ignoring null comparison rules

NULL = NULL is not true in SQL. If nullable columns are part of a match rule, define the intended behavior explicitly. Better still, use non-null business keys wherever possible.

Performance and indexing guidance

A clear statement can still perform poorly if Db2 must scan large tables. Review these points before moving a MERGE job into production:

• Create an index or unique constraint that supports the target columns in the ON clause.
• Index or organize the source data when a large staging table is repeatedly joined by the same key.
• Filter the source to the smallest valid set before the merge.
• Avoid updating unchanged rows when journaling volume, triggers, or replication traffic matters.
• Keep statistics current and inspect the access plan in ACS Run SQL Scripts.
• Test with realistic row counts—not only a five-row development sample.

For a deeper investigation, use Visual Explain and Db2 for i query optimization tools to confirm whether the target lookup uses the intended index.

MERGE, transactions, triggers, and journaling

MERGE is a data-change statement. The updates, inserts, or deletes it performs participate in your transaction according to the connection’s commitment-control settings. If the merge belongs with other related changes, process them as one business transaction and use COMMIT or ROLLBACK appropriately.

Target-table triggers can fire for the actions performed by the merge, so include their cost and business effects in testing. Review Db2 for i triggers and IBM i commitment control when the workload is transactional or audited.

MERGE vs separate UPDATE and INSERT statements

For staging-table synchronization, MERGE is usually the clearest starting point. Separate statements can still be correct when requirements demand different restart points, auditing steps, or exception handling.

Db2 for i MERGE checklist

• Identify a stable business key.
• Confirm that the source contains at most one winning row per key.
• Index the target match columns.
• Update only mutable attributes.
• Add conditions to avoid stale or unnecessary updates.
• Decide what should happen to target-only rows.
• Test triggers, journaling, commitment control, and restart behavior.
• Inspect the access plan with production-like data volumes.

Frequently asked questions

Is MERGE the same as UPSERT in Db2 for i?

UPSERT is the common name for “update if matched, insert if not matched.” In Db2 for i, MERGE is the SQL statement normally used to implement that pattern.

Can Db2 for i MERGE delete rows?

Yes, MERGE can support a delete action for matched rows when its condition is satisfied. Use deletion carefully and verify the exact syntax available on your IBM i release before deploying it.

Can I use a CTE or SELECT as the MERGE source?

Yes. A query, derived table, or CTE is often the best way to filter, transform, or deduplicate source data before applying changes to the target.

Does MERGE commit automatically?

Not inherently. Commit behavior depends on the SQL environment, connection settings, and commitment-control configuration. Treat the merge as part of the surrounding transaction.

Why does a MERGE fail when the source contains duplicates?

Multiple source rows may try to modify the same target row, making the result ambiguous. Deduplicate the source by its business key before running the merge.

Final takeaway

The Db2 for i MERGE statement is a concise, set-based way to synchronize IBM i data. Define a reliable match key, clean the source, index the target, and make each action explicit. With those controls in place, MERGE can replace repetitive update-and-insert logic with SQL that is easier to read, test, and maintain.

Official reference: IBM: Merging data with Db2 for i SQL.