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?
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.
Syntax. Following is the basic syntax of INSERT INTO statement. INSERT INTO tableName { [ ( columnName [,...] ) ] { VALUES { ( { DEFAULT | expression } [,...] ) }
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 } [,...] ) }
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With