PL/SQL INSERT ON DUPLICATE KEY UPDATE

In database management, maintaining data integrity while inserting new records is a common challenge. we may want to insert a new record, but if it already exists based on a unique key, we need to update the existing record. This process is known as "Upsert".

In this article, we will explain how to perform PL/SQL INSERT ON DUPLICATE KEY UPDATE using the MERGE statement. We’ll break down the syntax, explain each key term, and provide real-world examples to illustrate how to handle Upsert efficiently in Oracle databases.

PL/SQL INSERT ON DUPLICATE KEY UPDATE

In Oracle, the MERGE statement can be used to recreate the INSERT ON DUPLICATE KEY UPDATE functionality, which allows for an upsert operation. This means that if a matching record is found based on a condition, it gets updated. If no match is found, a new record is added. The MERGE statement allows both inserting and updating.

Syntax:

MERGE INTO target_table t
USING (SELECT :value1 AS key_column, :value2 AS column_value FROM dual) s
ON (t.key_column = s.key_column)
WHEN MATCHED THEN
    UPDATE SET t.column_value = s.column_value
WHEN NOT MATCHED THEN
    INSERT (key_column, column_value)
    VALUES (s.key_column, s.column_value);

Key Terms:

• MERGE INTO target_table: The table where the operation is being performed.
  
• USING (SELECT ... FROM dual): Provides the source data for comparison.
  
• ON (t.key_column = s.key_column): Specifies the condition for matching records.
  
• WHEN MATCHED: Defines the update operation if the condition matches.
  
• WHEN NOT MATCHED: Defines the insert operation if no match is found.

Examples of PL/SQL INSERT ON DUPLICATE KEY UPDATE

Let's explain examples to demonstrate how to implement this approach in Oracle databases. We will first examine how to update an existing record if a matching entry is found. Then, we will see how to insert a new record when no match exists, using the MERGE statement for both scenarios.

Example 1: Using MERGE for Upsert

Let's assume we have a table called Products that stores product information. If a product with the same product_id already exists, we want to update the price. If it does not exist, a new record should be inserted.

Query:

 -- Create the Products table
CREATE TABLE Products (
    product_id INT PRIMARY KEY,            -- Primary key for product identification
    product_name VARCHAR2(50),            -- Name of the product
    price NUMBER(10, 2)                   -- Price of the product
);

-- Inserting initial data
INSERT INTO Products (product_id, product_name, price)
VALUES (101, 'Laptop', 1200);            -- Insert a new product with price 1200

-- Upsert operation using MERGE
MERGE INTO Products t
USING (SELECT 101 AS product_id, 'Laptop' AS product_name, 1250 AS price FROM dual) s
ON (t.product_id = s.product_id)         -- Condition to check if the product exists
WHEN MATCHED THEN
    UPDATE SET t.price = s.price         -- Update the price if the product exists
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price)
    VALUES (s.product_id, s.product_name, s.price);  -- Insert new product if it does not exist

Output:

Explanation:

• The MERGE statement checks if the product with product_id = 101 already exists.
  
• Since the product exists, it updates the price from 1200 to 1250.
  
• If the product does not exist, the WHEN NOT MATCHED clause would insert a new row.

Example 2: Inserting a New Record

In this example, we perform an INSERT ON DUPLICATE KEY UPDATE operation where the product ID does not exist in the table, leading to an insert operation. Hence, the operation will insert a new record.

Query:

 -- Upsert operation using MERGE for a new product
MERGE INTO Products t
USING (SELECT 102 AS product_id, 'Smartphone' AS product_name, 700 AS price FROM dual) s
ON (t.product_id = s.product_id)         -- Condition to check if the product exists
WHEN MATCHED THEN
    UPDATE SET t.price = s.price         -- Update the price if the product exists
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price)
    VALUES (s.product_id, s.product_name, s.price);  -- Insert new product if it does not exist

Output:

Explanation:

• Since no product with product_id = 102 exists, a new row is inserted with the details of the smartphone.
  
• Since no product with that ID exists, the WHEN NOT MATCHED clause inserts the new product with the values provided.

Another Approach: Conditional INSERT and UPDATE

If we don't want to use the MERGE statement, another method to perform an upsert operation is by combining INSERT and UPDATE statements conditionally using PL/SQL logic.

Query:

BEGIN
    -- Attempt to update the price for the existing product
    UPDATE Products
    SET price = 1500
    WHERE product_id = 101;                -- Condition to check if the product exists

    -- Check if the update affected any rows
    IF SQL%ROWCOUNT = 0 THEN
        -- Insert a new product if no rows were updated
        INSERT INTO Products (product_id, product_name, price)
        VALUES (101, 'Laptop', 1500);      -- Insert new product if it does not exist
    END IF;
END;

Explanation:

• The UPDATE statement tries to update the price of the product.
  
• If no rows are affected (SQL%ROWCOUNT = 0), an INSERT statement is executed to add a new row.

Conclusion

Using the PL/SQL MERGE statement for INSERT ON DUPLICATE KEY UPDATE operations is a powerful way to maintain data integrity and ensure database consistency. The MERGE statement efficiently handles both inserts and updates, making it a valuable tool for managing large datasets and avoiding duplicates.