Oracle UPSERT with MERGE: Insert or Update Rows

Oracle Database uses MERGE to update rows that match a condition and insert rows that do not. This insert-or-update pattern is commonly called an UPSERT. A primary key or unique constraint on the matching key protects the table from duplicate values. See Oracle’s MERGE statement reference for the full grammar and restrictions.

Create a table with a unique key

The sku primary key defines when an incoming row matches an existing inventory row:

CREATE TABLE inventory (
  sku VARCHAR2(30) CONSTRAINT inventory_pk PRIMARY KEY,
  product_name VARCHAR2(100) NOT NULL,
  quantity NUMBER(10) DEFAULT 0 NOT NULL
);

Insert or update one row with MERGE

Use a one-row SELECT from DUAL as the source. Bind variables supply the values:

MERGE INTO inventory target
USING (
  SELECT
    :sku AS sku,
    :product_name AS product_name,
    :quantity AS quantity
  FROM dual
) source
ON (target.sku = source.sku)
WHEN MATCHED THEN
  UPDATE SET
    target.product_name = source.product_name,
    target.quantity = source.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, product_name, quantity)
  VALUES (source.sku, source.product_name, source.quantity);

If sku exists, Oracle updates its name and quantity. If there is no match, Oracle inserts a new inventory row. Keep the ON condition focused on the key that identifies the target row; put update-only or insert-only filters in the corresponding WHEN clause.

Use MERGE with a staging table

For a batch, replace the one-row DUAL source with a query over a staging table:

MERGE INTO inventory target
USING incoming_inventory source
ON (target.sku = source.sku)
WHEN MATCHED THEN
  UPDATE SET
    target.product_name = source.product_name,
    target.quantity = source.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, product_name, quantity)
  VALUES (source.sku, source.product_name, source.quantity);

Make sure the source contains at most one row for each target key. Oracle defines MERGE as deterministic and raises an error if the same target row would be updated more than once in a statement.

Common MERGE mistakes

• No unique constraint on the business key: MERGE uses the ON condition to match rows, but a table constraint is still needed to enforce uniqueness for other sessions and statements.
• Matching on a mutable value: use the stable key in ON. A status or timestamp condition can make an existing row appear unmatched and lead to an insert attempt.
• Duplicate source keys: deduplicate or aggregate the source before merging so one source row maps to each target key.
• Updating a column used in ON: Oracle does not allow the MERGE update clause to change a target column referenced in the ON condition.

When connecting from Python, use bind variables rather than formatting values into SQL text; see Python Oracle CRUD with python-oracledb.

For UPSERT syntax in other databases, see MySQL, MariaDB, PostgreSQL, SQLite, and SQL Server.

For a side-by-side overview of these dialects, see SQL UPSERT Syntax by Database.