SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | MERGE Statement

The MERGE statement, often called "upsert" (a blend of update and insert), is used to either insert new records or update existing ones depending on whether a particular condition (usually a match on a primary key or unique constraint) is met. It's especially useful when you're working with data synchronization scenarios.

The MERGE statement is supported by several RDBMS like Oracle, SQL Server, and DB2. However, not every database supports the MERGE statement, and the exact syntax might differ slightly.

Here's a general overview of the MERGE statement:

Syntax (Oracle, SQL Server):

MERGE INTO target_table USING source_table
ON (condition) -- Often a condition to match records, e.g., target_table.id = source_table.id
WHEN MATCHED THEN 
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ... ) VALUES (value1, value2, ...);

Example:

Imagine you have a target table employees and a source table new_data. You want to update the records in employees based on the matching id from new_data or insert the data if there isn't a match:

MERGE INTO employees AS tgt
USING new_data AS src
ON (tgt.id = src.id)
WHEN MATCHED THEN 
    UPDATE SET tgt.name = src.name, tgt.salary = src.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (src.id, src.name, src.salary);

This statement will go through each record in new_data:

  • If an id in new_data matches an id in employees, the corresponding record in employees will be updated.
  • If an id in new_data doesn't have a match in employees, a new record will be inserted into employees.

Notes:

  1. Some databases might support additional clauses, such as WHEN NOT MATCHED BY SOURCE or WHEN NOT MATCHED BY TARGET, providing more flexibility.

  2. Since the MERGE statement can potentially modify multiple rows in different ways, be cautious with triggers that might fire as a result of these changes.

  3. As always, it's a good idea to test the MERGE statement in a controlled environment before using it on production data.

  4. Databases like PostgreSQL and MySQL don't natively support the MERGE statement but offer their mechanisms for upsert operations, like INSERT ... ON DUPLICATE KEY UPDATE for MySQL or INSERT ... ON CONFLICT for PostgreSQL.

  1. How to use MERGE in SQL:

    • Description: The SQL MERGE statement is used to perform conditional insert, update, or delete operations based on the condition specified in the ON clause.
    • Example Code:
      MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1);

  2. Merging data from source to target in SQL:

    • Description: Use the MERGE statement to synchronize data between a source and a target table based on a specified condition.
    • Example Code:
      MERGE INTO target_table AS target
USING source_table AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
    UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN
    INSERT (customer_id, name) VALUES (source.customer_id, source.name);

  3. Handling conflicts with the MERGE statement:

    • Description: Handle conflicts during the MERGE operation, such as updating a column only if certain conditions are met.
    • Example Code:
      MERGE INTO target_table AS target
USING source_table AS source
ON target.employee_id = source.employee_id
WHEN MATCHED AND source.salary > target.salary THEN
    UPDATE SET target.salary = source.salary;

  4. Performing upserts with the SQL MERGE statement:

    • Description: Use the MERGE statement to perform upserts (insert or update) based on whether a match is found or not.
    • Example Code:
      MERGE INTO target_table AS target
USING source_table AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET target.price = source.price
WHEN NOT MATCHED THEN
    INSERT (product_id, price) VALUES (source.product_id, source.price);

  5. Comparing MERGE with INSERT and UPDATE in SQL:

    • Description: Compare the usage of MERGE with separate INSERT and UPDATE statements, highlighting the advantages of MERGE.
    • Example Code:
      -- Using MERGE
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1);

-- Equivalent using separate INSERT and UPDATE
UPDATE target_table
SET column1 = (SELECT column1 FROM source_table WHERE target_table.id = source_table.id)
WHERE EXISTS (SELECT 1 FROM source_table WHERE target_table.id = source_table.id);

INSERT INTO target_table (id, column1)
SELECT id, column1 FROM source_table
WHERE NOT EXISTS (SELECT 1 FROM target_table WHERE target_table.id = source_table.id);

  6. Dynamic value for MERGE in SQL:

    • Description: Use dynamic values within the MERGE statement, such as using variables or parameters.
    • Example Code:
      DECLARE @source_value INT = 100;

MERGE INTO target_table AS target
USING (VALUES (@source_value)) AS source (column1)
ON target.id = source.column1
WHEN MATCHED THEN
    UPDATE SET target.column2 = 'Updated'
WHEN NOT MATCHED THEN
    INSERT (id, column2) VALUES (source.column1, 'Inserted');

  7. Using conditions in the SQL MERGE statement:

    • Description: Specify conditions using WHEN clauses in the MERGE statement to control the execution flow based on certain criteria.
    • Example Code:
      MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED AND source.status = 'Active' THEN
    UPDATE SET target.column1 = source.column1
WHEN NOT MATCHED AND source.status = 'Active' THEN
    INSERT (id, column1) VALUES (source.id, source.column1);

  8. Logging changes during a MERGE operation in SQL:

    • Description: Log changes made during a MERGE operation by capturing the affected rows and changes in a separate log table.
    • Example Code:
      CREATE TABLE merge_log (
    action VARCHAR(10),
    id INT,
    column1 VARCHAR(255),
    log_timestamp DATETIME
);

MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.column1
    OUTPUT 'UPDATE', inserted.id, inserted.column1, GETDATE() INTO merge_log
WHEN NOT MATCHED THEN
    INSERT (id, column1) VALUES (source.id, source.column1)
    OUTPUT 'INSERT', inserted.id, inserted.column1, GETDATE() INTO merge_log;

  9. Merging data in SQL tables with MERGE:

    • Description: Apply the MERGE statement to merge data within the same table, updating or inserting records based on a condition.
    • Example Code:
      MERGE INTO employees AS target
USING employees_backup AS source
ON target.employee_id = source.employee_id
WHEN MATCHED THEN
    UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, salary) VALUES (source.employee_id, source.salary);
Copyright © devanddep.com | All Rights Reserved