intro
Let’s learn everything you need to know about using MERGE in SQL query statements to master data synchronization at the database level.
Synchronization is one of the most delicate operations when dealing with two different sources. The end goal is to ensure that the target contains data merged from the source. In SQL, this typically requires conditional logic at the application level and several statements. Alternatively, you can achieve this by utilizing the MERGE statement in SQL query statements.
In this article, you will learn what the SQL MERGE statement is, how it works, and how to use it in a complete example.
Let’s dive in!
What Is the SQL MERGE Statement?
MERGE is used to perform conditional INSERT, UPDATE, or DELETE operations in a single SQL statement—something that would normally require multiple statements. At present, MERGE is only supported in PostgreSQL, SQL Server, and Oracle.
In most cases, you want to use MERGE in SQL query statements to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
In particular, MERGE matches rows from both tables based on a specified condition. If a match is found, it can update or delete the row in the target table, while non-matching rows from the source can be inserted.
MERGE in SQL Query: Syntax and First Examples
The syntax of the SQL MERGE statement in a standard syntax follows this structure:
1
MERGE INTO target_table AS target
2
USING source_table AS source
3
ON target.matching_column = source.matching_column
4
[WHEN MATCHED THEN
5
UPDATE SET target.column_1 = source.column_1, target.column_2 = source.column_2, target.column_n = source.column_n | DELETE]
6
[WHEN NOT MATCHED THEN
7
INSERT (column_1, column_2, column_n) VALUES (source.column_1, source.column_2, source.column_n);]
First, MERGE INTO target_table AS target specifies target_table as the table to be modified and assigns it the alias target for easy reference. Learn more in our guide on the SQL alias mechanism.
Then, USING source_table AS source defines source_table as the data source and assigns it the alias source. The ON clause indicates the join condition that determines matches between target and source rows. Only rows meeting this condition are eligible for actions under WHEN clauses:
In simpler terms, using MERGE in SQL query statements means performing a join from source_table to the target_table, producing zero or more candidate change rows. For each candidate change row, the query determines the status of MATCHED or NOT MATCHED, based on whether the condition after ON is met or not. Next, it executes the action in the selected WHEN clause. For each candidate change row, no more than one WHEN clause is executed.
Notes:
PostgreSQL MERGE Syntax
Here is the specific syntax of the PostgreSQL MERGE statement:
1
[ WITH with_query [, ...] ]
2
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
3
USING data_source ON join_condition
4
when_clause [...]
5
[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]
Where data_source is:
1
{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]
And when_clause is:
1
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
2
WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
3
WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }
And merge_insert is:
1
INSERT [( column_name [, ...] )]
2
[ OVERRIDING { SYSTEM | USER } VALUE ]
3
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }
And merge_update is:
1
UPDATE SET { column_name = { expression | DEFAULT } |
2
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
3
( column_name [, ...] ) = ( sub-SELECT )
4
} [, ...]
And merge_delete is:
1
DELETE
Specific considerations:
SQL Server MERGE Syntax
Check out the documentation for the SQL Server MERGE syntax.
Specific considerations:
Oracle MERGE Syntax
See the documentation for the Oracle MERGE syntax.
Specific considerations:
Complete Example of a MERGE Query in SQL
Now, we will see how to use MERGE in SQL query real-world scenarios.
Note: The queries below will be run in PostgreSQL, but you can easily adapt them to SQL Server, Oracle, or any other DBMS supporting MERGE. Also, the queries will be executed in DbVisualizer, a powerful PostgreSQL database client. Any other client will do.
Suppose that you have the employees and archive_employees tables in your company PostgreSQL database. employees contains partial data and is the target table, while cms_employees is the source table. cms_employees was imported into the current database from the CMS and contains updated data.
This is the data contained in employees:
The Data tab in DbVisualizer for employees
And this is the data contained in cms_employees:
Note that the two tables have a different structure.
Your goal is to update the employees table with the records from new_employees, inserting any new employees that are missing, and updating their salary and position based on the information in cms_employees.
Here is how you can do it with a MERGE query:
1
MERGE INTO employees AS e
2
USING cms_employees AS ce
3
ON e.full_name = CONCAT(ce.name, ' ', ce.surname)
4
WHEN MATCHED THEN
5
UPDATE SET position = ce.position, salary = ce.salary
6
WHEN NOT MATCHED THEN
7
INSERT (full_name, position, salary)
8
VALUES (CONCAT(ce.name, ' ', ce.surname), ce.position, ce.salary);
The above query synchronizes data between the employees and cms_employees tables. It matches records based on the concatenation of the name and surname from cms_employees to the full_name in employees. When a match is found, it updates the position and salary in employees. If no match is found, it inserts a new record into employees using the name, surname, position, and salary from cms_employees.
Run the query:
The query will affect 5 rows, updating 4 and adding 1. Verify that by taking a look at employees:
Note that all positions and salary were updated as desired from cms_employees to employees. Also, the “Maria Williams” record was added, as this did not appear in employees.
Now, assume that you also want to delete all employees records that are not present in cms_employees. Below is the query you need to write:
1
MERGE INTO employees AS e
2
USING cms_employees AS ce
3
ON e.full_name = CONCAT(ce.name, ' ', ce.surname)
4
WHEN MATCHED THEN
5
UPDATE SET position = ce.position, salary = ce.salary
6
WHEN NOT MATCHED THEN
7
INSERT (full_name, position, salary)
8
VALUES (CONCAT(ce.name, ' ', ce.surname), ce.position, ce.salary)
9
WHEN NOT MATCHED BY SOURCE THEN
10
DELETE;
This time, the result will be as follows:
The “Bob Johnson” record was removed as expected.
Et voilà! You are now a master of using MERGE in SQL query statements.
Conclusion
In this guide, you learned what the SQL MERGE statement is and how to use it in PostgreSQL, SQL Server, and Oracle. You now know that it enables you to perform conditional INSERT, UPDATE, or DELETE operations with a single SQL statement.
As shown here, DbVisualizer simplifies managing MERGE queries. This powerful database client supports multiple DBMS technologies and offers advanced features like visual data exploration, query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is the MERGE statement part of the SQL standard?
Yes, the MERGE statement is part of the SQL ISO/ANSI standard. It was introduced in SQL:2003 and extended even further in SQL:2008. As of this writing, the MERGE statement is supported by PostgreSQL, SQL Server, and Oracle.
How to merge two queries in SQL?
To merge two queries in SQL, you can use the SQL UNION and SQL UNION ALL operators. UNION combines results from both queries, removing duplicate rows, while UNION ALL includes all rows, keeping duplicates. Both queries must have the same number of columns and compatible data types in corresponding columns.
How does the SQL Server MERGE statement work?
The SQL Server MERGE statement works like that of PostgreSQL and Oracle, allowing conditional updates, insertions, or deletions in a target table based on data from a source table. It compares rows in both tables using a specified key and performs actions such as UPDATE, INSERT, or DELETE depending on the matches found.
How to use MERGE in MySQL?
MySQL does not directly support the SQL MERGE statement. For similar results on insertions, you can use the INSERT ... ON DUPLICATE KEY UPDATE syntax to perform upsert operations.
How to use CTEs with MERGE in SQL query statements?
You can use a CTE (Common Table Expression) with a MERGE statement in PostgreSQL and SQL Server by defining the CTE before the MERGE. The CTE can provide a subset of data or transformed data to be used in the MERGE query for matching, updating, or inserting rows.
