Upserting a row into an hsqldb table using the MERGE SQL statement

Question

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.

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

Answer 1

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)

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.