0

I am using the H2 file database and I am running into the following problem. Suppose I have a table called PROPERTIES with two VARCHAR(255) columns: NAME and VALUE, with NAME being the primary key. Now, I would like to update the table with a new row only if it doesn't exist. If it exists, I don't want to change the VALUE for that NAME. This would be easy on say MS SQL Server, but the H2 SQL does not have IF, WHERE NOT EXISTS. It does have a command called MERGE but that updates if exists. Is there a way to do this with H2 SQL?
I don't believe this question is a duplicate because I haven't seen it asked for H2 specifically.

Maciek Nicewicz
  • 61
  • 2
  • 9
  • It seems you can use `where not exists` with H2 http://stackoverflow.com/questions/19768051/h2-sql-database-insert-if-the-record-does-not-exist – Aaron Dietz Mar 31 '16 at 21:32
  • Actually it doesn't work for me. It doesn't seem that you can use WHERE NOT EXISTS in H2. I am using SQuirrel 3.6 to run the query (H2 driver is h2-1.4.187) – Maciek Nicewicz Mar 31 '16 at 22:06

1 Answers1

0

I'm not familiar with H2 but if "where not exists" does not work, how about checking if the new value already exists and inserting only if it doesn't?

This is a SQL Server example so you may need to change the syntasx.

Insert into Properties
(Name, Value)
source.Newname, source.NewValue
from Source
where NewName 
not in (
select Name
from Properties
)
;
Adam
  • 51
  • 6
  • Thanks, but your code seems to insert from another table. I need to be able to insert constant values directly into the table. How would I populate Source? – Maciek Nicewicz Apr 01 '16 at 16:57
  • How are you passing in the values? Are we using variables? With variables you could remove the "from Source" clause and replace the NewName and NewValue column values with the variables. – Adam Apr 01 '16 at 19:31