H2 Database - Merge

MERGE command is used to update the existing rows and insert new rows into a table. The primary key column plays an important role while using this command; it is used to find the row.

Syntax

Following is the generic syntax of the MERGE command.

MERGE INTO tableName [ ( columnName [,...] ) ] 
[ KEY ( columnName [,...] ) ] 
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select } 

In the above syntax, the KEY clause is used to specify the primary key column name. Along with VALUES clause, we can use primitive values to insert or we can retrieve and store another table values into this table using the select command.

Example

In this example, let us try to add a new record into Customers table. Following are the details of the new record in the table.

Using the following query, let us insert the given record into the H2 database query.

MERGE INTO CUSTOMER KEY (ID) VALUES (8, 'Lokesh', 32, 'Hyderabad', 2500);

The above query produces the following output.

Update count: 1 

Let us verify the records of the Customer table by executing the following query.

SELECT * FROM CUSTOMER;

The above query produces the following output.

Now let us try to update the record using the Merge command. Following are the details of the record to be updated.

Use the following query to insert the given record into the H2 database query.

MERGE INTO CUSTOMER KEY (ID) VALUES (8, 'Loki', 32, 'Hyderabad', 3000);

The above query produces the following output.

Update count: 1 

Let us verify the records of the Customer table by executing the following query.

SELECT * FROM CUSTOMER; 

The above query produces the following output −