How to Perform an Upsert Operation in Oracle Using MERGE?

Oracle Upsert: Efficiently Updating or Inserting Data

Problem:

How can you perform an upsert operation in Oracle—updating a record if it exists, or inserting a new one if it doesn't?

Solution: The MERGE Statement

Oracle doesn't have a dedicated UPSERT command. The MERGE statement provides this functionality. It efficiently combines update and insert operations based on a join condition.

Illustrative Example

This example showcases an upsert using the MERGE statement within a stored procedure:

CREATE OR REPLACE PROCEDURE upsert_data (p_id NUMBER, p_value NUMBER)
AS
BEGIN
  MERGE INTO my_table target
  USING dual source
  ON (target.id = p_id)
  WHEN MATCHED THEN
    UPDATE SET target.value = p_value
  WHEN NOT MATCHED THEN
    INSERT (id, value) VALUES (p_id, p_value);
END;
/

-- Sample usage and verification
CREATE TABLE my_table (id NUMBER PRIMARY KEY, value NUMBER);

BEGIN
  upsert_data(1, 10);
  upsert_data(1, 20);  -- Update existing row
  upsert_data(2, 30);  -- Insert new row
END;
/

SELECT * FROM my_table;

This procedure, upsert_data, takes an ID and a value. The MERGE statement compares the provided p_id with existing IDs in my_table.

• WHEN MATCHED: If a match is found, the value column is updated to p_value.
• WHEN NOT MATCHED: If no match is found, a new row is inserted with the given p_id and p_value.

The final SELECT statement demonstrates the combined update and insert results. This approach is efficient and avoids the need for separate UPDATE and INSERT statements with conditional checks.

The above is the detailed content of How to Perform an Upsert Operation in Oracle Using MERGE?. For more information, please follow other related articles on the PHP Chinese website!