MERGE Statement in SQL

Example

Let's consider an example. Suppose we have two tables: employees and employee_updates. The employees table contains the current employee information, while the employee_updates table contains the updated information for some employees.

-- Employees table

CREATE TABLE employees1 (

    employee_id INT PRIMARY KEY,

    name VARCHAR(50),

    department VARCHAR(50),

    salary DECIMAL(10, 2)

);

INSERT INTO employees1 (employee_id, name, department, salary) VALUES

  (1, 'Rahul', 'IT', 50000.00);

INSERT INTO employees1 (employee_id, name, department, salary) VALUES

  (2, 'Rinki', 'HR', 60000.00);

INSERT INTO employees1 (employee_id, name, department, salary) VALUES

  (3, 'Harsh', 'Finance', 55000.00);

-- Employee Updates table

CREATE TABLE employee_updates (

    employee_id INT,

    name VARCHAR(50),

    department VARCHAR(50),

    salary DECIMAL(10, 2)

);

INSERT INTO employee_updates (employee_id, name, department, salary) VALUES

  (1, 'Rahul', 'IT', 55000.00);

INSERT INTO employee_updates (employee_id, name, department, salary) VALUES

  (3, 'Harsh', 'Finance', 60000.00);

INSERT INTO employee_updates (employee_id, name, department, salary) VALUES

  (4, 'Sanjana', 'Marketing', 50000.00);

Now, let's use the MERGE statement to update the employees table based on the information in the employee_updates table:

MERGE INTO employees1 e

USING employee_updates u

ON e.employee_id = u.employee_id

WHEN MATCHED THEN

    UPDATE SET e.name = u.name, e.department = u.department, e.salary = u.salary

WHEN NOT MATCHED THEN

    INSERT (employee_id, name, department, salary)

    VALUES (u.employee_id, u.name, u.department, u.salary);

In this example:

1. The MERGE INTO clause specifies the target table employees1 with the alias e.
    
2. The USING clause specifies the source table employee_updates with the alias u.
    
3. The ON clause defines the merge condition, which matches the employee_id from both tables.
    
4. The WHEN MATCHED THEN clause updates the matched rows in the employees table with the corresponding values from the employee_updates table.
    
5. The WHEN NOT MATCHED THEN clause inserts new rows into the employees table for the records that exist in the employee_updates table but not in the employees table.
    

After executing the MERGE statement, the employees table will be updated with the changes from the employee_updates table, and any new employees will be inserted.

Before executing the MERGE statement, the employees1 table contains the following data:

After executing the MERGE statement, the employees table will be updated as follows:

Let’s see what happened during the MERGE operation:

1. The row with employee_id = 1 in the employees table matched the row in the employee_updates table. The name, department, and salary columns were updated based on the values from the employee_updates table.
    
2. The row with employee_id = 2 in the employees table did not have a matching row in the employee_updates table, so it remained unchanged.
    
3. The row with employee_id = 3 in the employees table matched the row in the employee_updates table. The salary column was updated based on the value from the employee_updates table.
    
4. The row with employee_id = 4 did not exist in the employees table but was present in the employee_updates table. A new row was inserted into the employees table with the values from the employee_updates table.

Frequently Asked Questions

Can the MERGE statement be used to update & insert rows simultaneously?

Yes, the MERGE statement allows you to update existing rows & insert new rows in a single statement based on the specified conditions.

Is it necessary to have both the WHEN MATCHED THEN & WHEN NOT MATCHED THEN clauses in a MERGE statement?

No, you can use either one or both clauses depending on your requirements. You can have only the WHEN MATCHED THEN clause to update existing rows or only the WHEN NOT MATCHED THEN clause to insert new rows.

Can the MERGE statement be used with multiple source tables?

Yes, you can use joins or subqueries in the USING clause to merge data from multiple source tables into the target table.

Conclusion

In this article, we learned about the MERGE statement in SQL, which provides a powerful & efficient way to perform update, insert, & delete operations in a single statement. We talked about the syntax of the MERGE statement & understood its various components. With the help of a proper example,, we saw how the MERGE statement can be used to update existing rows & insert new rows based on the matching condition.

You can also practice coding questions commonly asked in interviews on Coding Ninjas Code360. 

Also, check out some of the Guided Paths on topics such as Data Structure and Algorithms, Competitive Programming, Operating Systems, Computer Networks, DBMS, System Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.