Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 723
Alison Avatar asked Mar 08 '13 03:03

Alison


People also ask

How do you insert if not exist?

There are three ways you can perform an “insert if not exists” query in MySQL: Using the INSERT IGNORE statement. Using the ON DUPLICATE KEY UPDATE clause. Or using the REPLACE statement.

How do I add data to my H2 database?

Syntax. Following is the basic syntax of INSERT INTO statement. INSERT INTO tableName { [ ( columnName [,...] ) ] { VALUES { ( { DEFAULT | expression } [,...] ) }

How do I add a column to my H2 database?

Syntax. Following is the generic syntax of the Alter Table Add command. ALTER TABLE [ IF EXISTS ] tableName ADD [ COLUMN ] { [ IF NOT EXISTS ] columnDefinition [ { BEFORE | AFTER } columnName ] | ( { columnDefinition } [,...] ) }


1 Answers

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.

like image 62
JodyT Avatar answered Oct 05 '22 02:10

JodyT