H2 Database - Merge

Donald Green Donald Green
Feb 3, 2022 - 02:00
0 21
H2 Database - Merge

The MERGE command is used to update existing rows and insert new rows into a table. The primary key column plays an important role when using this command; it is used to search for a string.

Syntax

Following is the general 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 the VALUES clause, we can use primitive values ​​to insert, or retrieve and store other table values ​​in that table using the select command.

example

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

.

Column name Meaning
I'D eight
TITLE Lokesh
AGE 32
THE ADDRESS Hyderabad
SALARY 2500

Using the following query, let's insert the given record into an H2 database query.

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

The above query results in the following output.

Update count: 1

Let's check the records of the Customer table by running the following query.

SELECT * FROM CUSTOMER ;

The above query results in the following output.

I'D title Age The address Salary
one Ramesh 32 Ahmedabad 2000
2 Khilan 25 Delhi 1500
3 kaushik 23 Kota 2000
4 Chaitali 25 Mumbai 6500
5 hardik 27 Bhopal 8500
6 Komal 22 member of parliament 4500
7 Muffy 24 Indore 10000
eight Lokesh 32 Hyderabad 2500

Now let's try to update the record using the Merge command . Below are the details of the entry to be updated.

Column name Meaning
I'D eight
TITLE Loki
AGE 32
THE ADDRESS Hyderabad
SALARY 3000

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

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

The above query results in the following output.

Update count: 1

Let's check the records of the Customer table by running the following query.

SELECT * FROM CUSTOMER;

The above query results in the following output: