Upsert in HSQL
March 21, 2018
I'm using HSQLDB for a configuration database during a JUnit test run and I was having trouble with multiple tests trying to insert the same configuration. What I needed to do was only insert into the database if the row didn't already exist, and if it did, then I should update instead.
This can be achieved with the following SQL:
MERGE INTO configuration
USING (VALUES('%s', '%s')) I (parameter_key, parameter_value)
ON configuration.parameter_key = I.parameter_key
WHEN MATCHED THEN
UPDATE SET configuration.parameter_value = I.parameter_value
WHEN NOT MATCHED THEN
INSERT (parameter_key, parameter_value) VALUES(I.parameter_key, I.parameter_value)
