Когда совпали SQL

В SQL, ключевое слово "WHEN MATCHED" используется в операции объединения таблиц для определения действий, которые должны выполниться, когда строки таблиц-источников соответствуют строкам таблиц-целей.

Пример:

MERGE INTO target_table
USING source_table
ON (target_table.id = source_table.id)
WHEN MATCHED THEN
    UPDATE SET target_table.column = source_table.column
    DELETE WHERE source_table.column = 'X';
    

Understanding the "WHEN MATCHED" Clause in SQL

When it comes to SQL, the "WHEN MATCHED" clause plays an important role in performing efficient and effective data manipulation operations. This clause is typically used in conjunction with the "MERGE" statement, which allows you to combine the functionality of INSERT, UPDATE, and DELETE operations within a single SQL statement. In this article, we will delve into the details of the "WHEN MATCHED" clause and explore its practical applications.

What is the "WHEN MATCHED" Clause?

The "WHEN MATCHED" clause is a part of the "MERGE" statement in SQL. It is specifically used to define the action that needs to be taken when a matched row is found during the merging process. A matched row refers to a row in the source table that has a matching key value with a row in the target table.

Understanding the Syntax

The syntax of the "WHEN MATCHED" clause is as follows:

WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2
    WHERE condition;

In this syntax, the UPDATE statement is used to specify the action that needs to be performed when a match is found. You can update one or more columns in the target table depending on your requirements. Additionally, you can also include a WHERE clause to define specific conditions under which the update should occur.

Example Usage of the "WHEN MATCHED" Clause

Let's consider a practical example to understand the usage of the "WHEN MATCHED" clause. Suppose we have two tables: source_table and target_table. We want to update the values of the salary column in the target_table with the values from the salary column in the source_table, based on a matching employee_id.

MERGE INTO target_table
USING source_table
ON (target_table.employee_id = source_table.employee_id)
WHEN MATCHED THEN
    UPDATE SET target_table.salary = source_table.salary;

In this example, we are combining the data from the source_table and target_table using the common column employee_id. The WHEN MATCHED clause specifies that when a matching row is found, we want to update the salary column in the target_table with the value from the salary column in the source_table.

Benefits of Using the "WHEN MATCHED" Clause

The "WHEN MATCHED" clause provides several benefits that make it a powerful tool for performing merging operations:

• Efficiency: By using the "MERGE" statement with the "WHEN MATCHED" clause, you can avoid writing multiple SQL statements for performing insertions, updates, and deletions separately. This can improve the overall performance of your database operations.
• Flexibility: The "WHEN MATCHED" clause allows you to define specific conditions for updating the target table. This flexibility enables you to tailor the merging process according to your specific requirements.
• Concurrency Control: The "MERGE" statement with the "WHEN MATCHED" clause provides a concise and efficient way to handle concurrent updates from different sources. It ensures that only the specified updates are performed when a match is found, reducing the chances of conflicts or data inconsistencies.

By leveraging the power of the "WHEN MATCHED" clause, you can simplify your data manipulation tasks and streamline your SQL queries.