PL/SQL INSERT ON DUPLICATE KEY UPDATE
Last Updated : 11 Oct, 2024
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:
| product_id | product_name | price |
|---|
| 101 | Laptop | 1250 |
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:
| product_id | product_name | price |
|---|
| 101 | Laptop | 1250 |
| 102 | Smartphone | 700 |
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.
Similar Reads
How to Find Duplicate Rows in PL/SQL
Finding duplicate rows is a widespread requirement when dealing with database analysis tasks. Duplicate rows often create problems in analyzing tasks. Detecting them is very important. PL/SQL is a procedural extension for SQL. We can write custom scripts with the help of PL/SQL and thus identifying
5 min read
Python SQLite - Insert Data
In this article, we will discuss how can we insert data in a table in the SQLite database from Python using the sqlite3 module. The SQL INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using the INSERT INTO statement for inserting rows: Only values: The firs
3 min read
Update Date Field in SQL Server
The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using the UPDATE statements as per our requirement. With this article, we will learn how to Update the Date Field in SQL Server. In this article, we w
2 min read
MySQL Insert Date Time
In today's world, working with data is now a data-to-data activity, so therefore managing data with proper data and time is also crucial. MySQL provides functionalities to handle data and time properly in the database. Understanding how to insert data and time into MySQL database with functions prov
4 min read
PL/SQL Insert Dates
When managing databases, handling date and time data accurately is important. Date fields are frequently used for storing important information such as user birth dates, product release dates, and event timelines. Oracle PL/SQL provides robust support for date operations, ensuring accurate data stor
4 min read
SQLite – INSERT IF NOT EXISTS ELSE UPDATE
SQLite is a popular choice for embedded database applications due to its lightweight nature and ease of use. One common scenario in SQLite is the need to insert a row into a table if it does not already exist and update it if it does. In this article, We will explore how to achieve this efficiently
4 min read
When to Use ON UPDATE CASCADE in PL/SQL?
In Oracle PL/SQL, managing the update of related records in child tables can be challenging, especially when dealing with complex data relationships. The ON UPDATE CASCADE option in Oracle provides a powerful solution to automate the update of child records whenever the corresponding parent record i
4 min read
PL/SQL Insert DateTimes
In PL/SQL, handling date and time values is a common requirement, whether we are working with databases that store transaction times, schedule events, or track user activities. Understanding how to insert these values correctly is crucial for accurate data management and retrieval. In this article,
4 min read
PL/SQL Cursor Update
PL/SQL stands for Procedural Language/Structured Query Language. It has block structure programming features. In Oracle PL/SQL, cursors play a vital role in managing and processing query results. Among the various types of cursors, updatable cursors stand out for their ability to fetch data and modi
5 min read
How to Find Duplicate Records in SQL?
To find duplicate records in SQL, we can use the GROUP BY and HAVING clauses. The GROUP BY clause allows us to group values in a column, and the COUNT function in the HAVING clause shows the count of the values in a group. Using the HAVING clause with a condition of COUNT(*) > 1, we can identify
3 min read