Insert into h2 table if not exists

问题:

I am using H2. I want to insert a value into a table if it does not exist. I create the table with:

CREATE TABLE IF NOT EXISTS $types 
  (type VARCHAR(15) NOT NULL UNIQUE);

And I want to do something like

REPLACE INTO types (type) values ('type1');

I found an example about Replace that apparently works for MySQL but I am using h2. But I get an error when I run this from my h2 console:

Syntax error in SQL statement "REPLACE[*] INTO TYPES (TYPE) VALUES ('expense') "; expected "ROLLBACK, REVOKE, RUNSCRIPT, RELEASE, {"; SQL statement:
REPLACE INTO types (type) values ('expense') [42001-170] 42001/42001

I also tried

INSERT IGNORE INTO types (type) values ('expense');

and

INSERT INTO types (type) values ('expense') ON DUPLICATE KEY UPDATE type=type;

I don't care if the new insert overwrites the old data or if it just does not perform the new insert. Is there a way to do this with h2 database?

回答1:

The merge statement should allow you to achieve what you want. I'm no expert on H2, but I've used the MERGE statement in SQL Server several times and from the looks of that website it should do the trick.

From the website:

Updates existing rows, and insert rows that don't exist. If no key column is specified, the primary key columns are used to find the row.