Extending MERGE in Oracle SQL: Strategies for Handling Unmatched Rows with Soft Deletes

Introduction and Problem Context

In database management systems, data synchronization is a common task, especially in distributed environments or reporting scenarios. Oracle SQL's MERGE statement provides a powerful way to merge data from a source table into a target table, supporting updates for matched rows and inserts for unmatched rows. However, in practical applications, more complex requirements may arise, such as when records are deleted in the source table, it may be undesirable to physically delete them from the target table. Instead, setting a flag (e.g., IsDeleted) to mark them as "soft deleted" can help retain historical data or meet audit requirements.

Consider a specific case: there is a main database (main) and a report database (report), and the main.TEST table needs to be synchronized to the report.TEST table. When records are deleted in main, the goal is to only set the IsDeleted flag to "Y" in report, rather than removing the records. The initial MERGE statement is as follows:

MERGE INTO report.TEST target
USING (SELECT * FROM main.TEST) source
   ON (target.ID = source.ID)
WHEN MATCHED THEN
    UPDATE SET (target... = source...)
WHEN NOT MATCHED THEN
    INSERT (...) VALUES (source...)
;

This statement handles new and updated records but cannot process records that have been deleted in the source table, as the WHEN NOT MATCHED clause only targets rows present in the source but not in the target. Therefore, a method is needed to update rows in the target table that have no match in the source.

Core Solution: Combining with an UPDATE Statement

Based on best practices (from the answer with a score of 10.0), the most elegant approach is to use a separate UPDATE statement to handle soft deletes. This method is straightforward, easy to understand, and maintain. Here is the implementation code:

UPDATE report.TEST target
SET    isDeleted = 'Y'
WHERE  NOT EXISTS (SELECT 1
                   FROM   main.TEST source
                   WHERE  source.ID = target.ID);

In this UPDATE statement, a NOT EXISTS subquery is used to identify records in report.TEST that do not have a corresponding ID in main.TEST. For these records, the isDeleted column is set to "Y", achieving a soft delete. This approach does not require modifying the existing MERGE statement; instead, it is executed as a subsequent step, ensuring modularity and clarity in the code.

From a performance perspective, the NOT EXISTS subquery is generally efficient, especially if the ID column is indexed. Oracle's query optimizer can effectively handle this anti-join operation, avoiding full table scans. However, for very large tables, considerations such as batch processing or other optimization techniques might be necessary, but this is beyond the scope of this article.

Analysis of Alternative Approaches

In addition to the best solution, other answers propose different methods, each with limitations. For example, an answer with a score of 3.2 demonstrates a MERGE statement example, but it focuses on inserting unmatched records within the same table and does not directly address soft deletes. Its code is:

MERGE INTO YOUR_TABLE d
USING (SELECT 1 FROM DUAL) m
    ON ( d.USER_ID = '123' AND d.USER_NAME= 'itszaif') 
WHEN NOT MATCHED THEN
        INSERT ( d.USERS_ID, d.USER_NAME)
        VALUES ('123','itszaif');

This example highlights basic usage of MERGE but does not provide a solution for updating rows unmatched in the source.

Another answer with a score of 2.6 attempts to integrate soft delete logic into the MERGE statement by extending the source data. It uses UNION ALL to add deleted records to the source, with a deleteMe flag. Then, a CASE statement in the UPDATE clause prevents updating deleted data. A code snippet is:

MERGE INTO target
USING
(
    --Source data
    SELECT id, some_value, 0 deleteMe FROM source
    --And anything that has been deleted from the source
    UNION ALL
    SELECT id, null some_value, 1 deleteMe
    FROM
    (
        SELECT id FROM target
        MINUS
        SELECT id FROM source
    )
) source
   ON (target.ID = source.ID)
WHEN MATCHED THEN
    UPDATE SET target.some_value =
        CASE WHEN deleteMe=1 THEN target.some_value ELSE source.some_value end
    ,isDeleted = deleteMe
WHEN NOT MATCHED THEN
    INSERT (id, some_value, isDeleted) VALUES (source.id, source.some_value, 0)

While this method is theoretically feasible, it increases query complexity with additional CASE statements and subqueries, potentially leading to performance degradation and difficult maintenance. Therefore, for most practical applications, a separate UPDATE statement is preferable.

Implementation Recommendations and Best Practices

When implementing data synchronization tasks, it is advisable to combine MERGE and UPDATE statements to handle all possible data change scenarios. Here is a complete example workflow:

1. First, use the MERGE statement to handle new and updated records. This ensures the target table stays synchronized with the source table for matched rows.
2. Then, execute the above UPDATE statement to mark soft-deleted records. This can be placed within the same transaction to ensure data consistency.
3. Consider adding error handling and logging to monitor the synchronization process and handle any exceptions.

From a code readability and maintainability perspective, this separation of concerns is superior to attempting to integrate all logic into a single MERGE statement. It makes the purpose of each step clear, facilitating debugging and optimization. Moreover, if future requirements change (e.g., needing physical deletion or additional cleanup), the UPDATE part can be easily modified or extended without affecting the MERGE logic.

Conclusion

In Oracle SQL, the MERGE statement is a powerful tool for data synchronization, but its default behavior may not meet all needs, such as in soft delete scenarios. By combining it with a separate UPDATE statement that uses a NOT EXISTS subquery to identify and mark rows unmatched in the source, this functionality can be elegantly achieved. This method, based on best practices, offers good performance, readability, and maintainability. While alternative approaches like extending source data exist, they often add complexity and are not recommended for most production environments. In summary, when designing and implementing data synchronization strategies, the simplest and most effective solution should be chosen based on specific requirements to ensure system reliability and efficiency.