Insert into h2 table if not exists

Question

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?

could you do a `DROP TABLE IF EXISTS` http://www.h2database.com/html/grammar.html#drop_table then start from scratch? — jchapa, Mar 08 '13 at 03:21
Thank you for the link to the grammar, it looks like there are no modifiers like the ones for MySQL on the Insert statement: http://www.h2database.com/html/grammar.html#insert. I don't think I will drop my table, though. — Alison, Mar 08 '13 at 03:24

score 26 · Accepted Answer · answered Mar 08 '13 at 03:27

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.

Insert into h2 table if not exists

1 Answers1

Linked