Efficiently Use MERGE Statements for Your Data Projects

Why is the MERGE Statement Useful for Alibaba Cloud Users?

To answer this question, we’ll need to consider the following situation:

What is the Command MERGE and What Can It Do?

The command MERGE is an important SQL statement command because It allows you to solve the recurring problem of blind data insert in databases. This issue refers to a data insert that is done regardless of the existence of previous data. This command allows you to prevent the need for going to a higher level language like PL/SQL, for example, allowing you to be able to write SQL directly.


Nowadays, the MERGE statement has been added to the standard SQL:2003, but not every Database Management System (DBMS) also include the upsert feature in the MERGE statement.

  • You’ll need to know how to use the UPDATE statement that allows you to make changes on existing entries.
  • You’ll need to know how to use the INSERT statement that allows you to insert one entry in the existing table or many line in a single step

What Is the Structure of the MERGE Statement?

To learn more about about the MERGE statement, you can check out Oracle's Database SQL Reference page on MERGE.

MERGE [indice]
INTO [schéma.] {table | voir} [t_alias]
USAGE [schéma.] {table | voir | sous-requête}
ON (condition)
UPDATE SET column = {expr |  DEFAULT }
[, column = {expr | DEFAUT }] ...
[DELETE where_clause]
INSERT [(column [, colonne] ...)]
values ({expr [, expr] ... | DEFAULT })
[Table INTO [schema.]]

Other Related Semantics

The Clause: INTO

Use the clause INTO to specify the target table in which you want to update or insert data. To merge the data in a view, that view must be able to be updated. You can take a look at "Views that can be updated" section to get more details.

The Clause USING

The clause USING is used to specify the source of data that needs to be inserted or updated. The source can be a table, a view, or the result of a request.

The Clause ON

We use the clause ON to specify the condition on which the MERGE operation to update or insert data will be applied. For every entry of the target table, for which the research condition is true, the Oracle database updates the target entry with the corresponding data of the source table. If the condition is not true for any line, the database is inserted in the target table based on the corresponding entry in the source table.

The Clause clause_update_merge

The clause clause_update_merge specifies the new values of the target column in the target table. Oracle's Database management system makes this update if the condition of the ON clause is true. If the update clause is ran, all the update hooks defined in the table will be activated.

  • You cannot specify DEFAULT when updating a view.

The Clause insert_clause_merge

The clause insert_clause_merge specifies the values to insert in the column of the target table if the condition of the ON clause is false. If the insert clause is run, all the insertion hooks defined on the table will be activated. If you omit the list of columns after the key word INSERT, the number of columns of the target table must correspond to the number of values of the VALUES clause.

The Clause logging_clause_error

The clause logging_clause_error has the same behavior in the statement MERGE as in a statement INSERT. You have to report to the statement INSERT logging_clause_error for more details.

Why Do We Use the Statement MERGE?

The database management systems can implement this functionality maybe in a standard manner, using a synonymous statement or using non-standard syntax. The options are listed below:

  • Uses the term UPSERT : Microsoft SQL Azure and MongoDB
  • Non standard syntax: MySQL, SQLite, Firebird, IBM DB2, and Microsoft SQL


Take the following example where the data table HR_RECORDS will be merged with the table EMPLOYEES.

MERGE INTO employees e​    USING hr_records h​    ON (e.id = h.emp_id)  WHEN MATCHED THEN​    UPDATE SET e.address = h.address  WHEN NOT MATCHED THEN​    INSERT (id, address)​    VALUES (h.emp_id, h.address);
MERGE INTO employees e​    USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h​    ON (e.id = h.emp_id)  WHEN MATCHED THEN​    UPDATE SET e.address = h.address  WHEN NOT MATCHED THEN​    INSERT (id, address)​    VALUES (h.emp_id, h.address);


Throughout this article, you have learned what the MERGE statement is, why it is used, and you have also learn about its syntax, have applied it to an example. I hope that this article is useful for those using Alibaba Cloud's big data services. The goal of this article was to shows how you can update your data in a table with one single step, avoiding the headaches you could have by trying to update data manually or by writing so many requests. Hopefully, this article has also showed you how you can optimize your data warehouse before running Alibaba's big data and machine learning algorithms.

Original Source



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store