SQL MERGE Statement Tutorial 🎯

Welcome to our deep dive into the SQL MERGE statement! In this comprehensive guide, we'll learn the ins and outs of this powerful tool. By the end, you'll be able to confidently use MERGE in your projects, making your SQL queries more efficient and effective.

Let's start by understanding why we need the MERGE statement. 📝

What is SQL MERGE?

The MERGE statement is a single SQL command that performs an INSERT, UPDATE, or DELETE operation on a table based on data from another table or query. In simpler terms, it allows you to perform multiple database operations in one go, making it a valuable tool for data management.

Prerequisites

To follow along, you should be familiar with the basics of SQL, including SQL statements such as SELECT, INSERT, UPDATE, and DELETE. If you're new to SQL, we recommend checking out our SQL Tutorial series on CodeYourCraft.

Basic Syntax 💡

The basic syntax of the MERGE statement is as follows:

sql

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

In this syntax:

target_table is the table you want to update or insert data into.
source_table is the table or query that provides the data for the update or insert operation.
ON (target_table.column = source_table.column) specifies the condition for matching rows between the target_table and source_table.
WHEN MATCHED THEN is used when there's a match between the target_table and source_table. In this section, you can specify the update actions.
WHEN NOT MATCHED THEN is used when there's no match between the target_table and source_table. In this section, you can specify the insert actions.

Practical Example 📝

Let's illustrate the MERGE statement with a practical example. Suppose we have two tables: employees and salary_updates.

sql

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary FLOAT
);

CREATE TABLE salary_updates (
    id INT PRIMARY KEY,
    employee_id INT,
    new_salary FLOAT,
    update_date DATE
);

Now, let's say we want to update the salaries of employees using the data in the salary_updates table. Here's how you might use the MERGE statement to do this:

sql

MERGE INTO employees e
USING salary_updates su
ON (e.id = su.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = su.new_salary;

In this example, we're merging the employees table (e) with the salary_updates table (su). The merge is based on the employee ID, and when there's a match, the employee's salary is updated with the new salary from the salary_updates table.

Inserting Data with MERGE 💡

You can also use the MERGE statement to insert data. Let's extend our example to include an insert operation:

sql

MERGE INTO employees e
USING salary_updates su
ON (e.id = su.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = su.new_salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (su.id, su.employee_name, su.new_salary);

In this example, when there's no match between the employees table and the salary_updates table, a new row is inserted into the employees table with the employee's ID, name (assuming we have a column named employee_name in salary_updates), and the new salary.

Quiz 💡

Wrapping Up 📝

In this lesson, we've explored the MERGE statement in SQL. You've learned its basic syntax, how to use it for updating and inserting data, and seen a practical example. With this knowledge, you're well-equipped to start using MERGE in your projects, making your SQL queries more efficient and effective.

As always, we encourage you to practice and experiment with the MERGE statement. The more you use it, the more comfortable you'll become. Happy coding! 💡