使用MERGE SQL语句将行更新到hsqldb表中 [英] Upserting a row into an hsqldb table using the MERGE SQL statement

问题描述

没有从文档中了解我应该如何使用它.

Did not understand from the documentation how am I supposed to use it.

说我有一行要添加到集合中，覆盖所有现有值(如果该行已存在).我没有行主键，但是有唯一键.

Say I have a row I wish to upsert into the collection, overriding any existing values, if the row is already present. I do not have the row primary key, but I do have a unique key.

任何人都可以告诉我将MERGE语句插入到HSQLDB表中的MERGE语句吗?

Can any one show me the MERGE statement that upserts such a row into an HSQLDB table?

推荐答案

HSQLDB的简单示例如下:

A simple example for HSQLDB is as follows:

CREATE TABLE B(ID INT UNIQUE, A_ID INT);

MERGE INTO B 
  USING (VALUES 2, 3) I (ID, A_ID) 
  ON (B.ID=I.ID)
  WHEN MATCHED THEN UPDATE SET B.A_ID = I.A_ID
  WHEN NOT MATCHED THEN INSERT (ID, A_ID) VALUES (I.ID, I.A_ID)

USING子句包含新数据. ON子句是匹配条件.笔记 此处不需要使用主键或唯一约束.任何匹配条件都可以. WHEN MATCHED和WHEN NOT MATCHED子句分别用于UPDATE和INSERT.在这个简单的示例中，引用了USING子句中的数据，但是您可以使用任何其他值进行插入或更新.

The USING clause contains the new data. The ON clause is the match condition. Note there is no requirement for a primary key or unique constraint to be used here. Any match condition will do. The WHEN MATCHED and WHEN NOT MATCHED clauses are used for UPDATE and INSERT respectively. In this simple example, the data from the USING clause is referenced, but you can insert or update with any other value.

这篇关于使用MERGE SQL语句将行更新到hsqldb表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！