Tuesday, August 15, 2023

How to UPDATE and INSERT into a table using ONE single statement?

 In Oracle PL/SQL, you can use the MERGE statement to perform both updates and inserts in a single operation. This is particularly useful when you want to synchronize data between two tables based on certain conditions. The MERGE statement essentially combines the functionality of UPDATE and INSERT into a single command. Here's the basic syntax:


MERGE INTO target_table 
USING source_table 
ON (condition) 
WHEN MATCHED THEN 
    UPDATE SET column1 = value1, column2 = value2 
WHEN NOT MATCHED THEN 
    INSERT (column1, column2) VALUES (value1, value2);

Let's break down the components:

  • target_table: The table you want to update or insert data into.

  • source_table: The table from which you're getting the data.

  • condition: The condition that determines whether a match is found between the target_table and the source_table.

  • WHEN MATCHED THEN UPDATE: Specifies the action to take when a match is found. You define the columns to update and their new values.

  • WHEN NOT MATCHED THEN INSERT: Specifies the action to take when no match is found. You define the columns to insert data into and their corresponding values.

Here's a simple example:

Assume you have a employees table and a employees_updates table. You want to update existing employee records based on their employee_id, and if the employee doesn't exist in the main table, you want to insert the data as a new record.


MERGE INTO employees e 
USING employees_updates u 
ON (e.employee_id = u.employee_id) 
WHEN MATCHED THEN 
    UPDATE SET e.first_name = u.first_name, e.last_name = u.last_name 
WHEN NOT MATCHED THEN 
    INSERT (employee_id, first_name, last_name)
    VALUES (u.employee_id,                 u.first_name, u.last_name);

In this example, if an employee in the employees_updates table matches an employee in the employees table based on their employee_id, the UPDATE clause will be executed. If there's no match, the INSERT clause will be executed.

Using the MERGE statement is a powerful way to efficiently manage updates and inserts in a single command, reducing the need for multiple SQL statements.

on