插入h2表(如果不存在) [英] Insert into h2 table if not exists

问题描述

我正在使用H2.我想在表中插入一个不存在的值.我用以下方法创建表:

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);

我想做

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

我找到了一个例子显然适用于MySQL，但我使用的是h2.但是当我从h2控制台运行它时，我得到了一个错误:

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

我也尝试过

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

和

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

我不在乎新插入是否会覆盖旧数据，或者只是不执行新插入.有没有办法用h2数据库来做到这一点?

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?

推荐答案

合并语句应该允许您实现您想要的.我不是H2方面的专家，但是我已经在SQL Server中多次使用MERGE语句，并且从该网站的外观来看，它应该可以解决问题.

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.

从网站上:

更新现有行，并插入不存在的行.如果未指定键列，则使用主键列查找行.

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.

这篇关于插入h2表(如果不存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！