How to UPSERT (update or insert into a table?)

UPSERT in Oracle - MERGE your data

For UPSERTs in Oracle, the mighty MERGE statement shines. Below is a general gist of how a MERGE statement should look:

MERGE INTO target_table USING source_table ON (condition)
WHEN MATCHED THEN 
    UPDATE SET column1 = value1 [, column2 = value2 ...]  -- If found: Update!
WHEN NOT MATCHED THEN 
    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]); -- If not: Insert!


Our source_table here could be a bona fide table, a subquery, or even the DUAL table if you're feeding in direct values. Here’s an example:

MERGE INTO your_table yt
USING DUAL  -- Single-row input. Pseudo-table for the win!
ON (yt.id = 1)
WHEN MATCHED THEN 
    UPDATE SET yt.data = 'new_data'  -- An update a day keeps data decay away!
WHEN NOT MATCHED THEN 
    INSERT (id, data) VALUES (1, 'new_data'); -- Into the ocean of data goes our new morsel.


Know thy UPSERT - Insights for Oracle

Navigating the UPSERT landscape in Oracle requires a discerning eye for performance tendon and reliability bone.

The DUAL's in the details

For UPSERTs, the DUAL table can be used as a single-row input source in the MERGE operation. Using DUAL is akin to a microsubquery, handy when working directly with values, not another table.

Duplication is the sincerest form of flattery

There might be times when MERGE might not meet your needs. An alternate strategy can be INSERT coupled with an exception handler for duplicates:

BEGIN
    INSERT INTO your_table (id, data) VALUES (1, 'new_data'); -- INSERT mode: Engaged!
EXCEPTION
    WHEN dup_val_on_index THEN  -- Whoops! Dupe spotted!
        UPDATE your_table SET data = 'new_data' WHERE id = 1; -- Smooth transition to UPDATE mode.
END;


Sounds cool, doesn't it? Think of it as trying to barge in a party. No entry (duplicate key error)? No problem, you already have a Plan B (update).

A/B Testing: Not just for Marketing

It is imperative to test your UPSERT logic before deploying to catch any snafus, particularly in concurrent environments where race conditions can backfire spectacularly, giving rise to unexpected duplicates or data loss.

Performance & Reliability - The dual pillars

Performance Metrics: MERGE vs separate UPDATE/INSERT

Depending on dataset size and system load, performance differences between using MERGE and separate UPDATE/INSERT operations could be significant. Be sure to measure and choose wisely.

Concurrency & Sync

Concurrency issues and ensuring data consistency are significant considerations while performing an UPSERT operation. Oracle's MERGE takes care of consistency with internal locking, but deep understanding of this behavior is critical to avoid data anomalies or even deadlocks.

Do not disturb

Unless necessary, resist the temptation to drop and recreate tables post-UPSERT. It often results in significant overhead, and in extreme cases, data loss might occur too.