H2 Database 简明教程

H2 Database - Merge

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

以下是 MERGE 命令的通用语法。

Following is the generic syntax of the MERGE command.

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

在上面的语法中，KEY 子句用于指定主键列名。除了 VALUES 子句，我们可以使用基元值插入或使用 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

在此示例中，我们尝试向 Customers 表中添加一条新记录。以下是表中新记录的详细信息。

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.

Column Name

Value

NAME

Lokesh

AGE

ADDRESS

Hyderabad

SALARY

2500

使用以下查询，让我们将给定记录插入到 H2 数据库查询中。

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

通过执行以下查询来验证 Customers 表中的记录。

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

SELECT * FROM CUSTOMER;

以上查询将产生以下输出。

The above query produces the following output.

ID	Name	Age	Address	Salary
1	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	MP	4500
7	Muffy	24	Indore	10000
8	Lokesh	32	Hyderabad	2500

现在让我们尝试使用 Merge 命令更新记录。以下是将要更新的记录的详细信息。

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

Column Name

Value

NAME

Loki

AGE

ADDRESS

Hyderabad

SALARY

3000

使用以下查询将给定记录插入到 H2 数据库查询中。

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

通过执行以下查询来验证 Customers 表中的记录。

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

SELECT * FROM CUSTOMER;

上面的查询生成以下输出 −

The above query produces the following output −

ID	Name	Age	Address	Salary
1	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	MP	4500
7	Muffy	24	Indore	10000
8	Loki	32	Hyderabad	3000