Oracle MERGE statement in H2 database

sql h2 sql-merge
user1877775 picture user1877775 · Dec 5, 2012 · Viewed 10.1k times · Source

We started to use the H2 in memory database for automated testing. We use Oracle for our production & dev environments. So the idea is to duplicate the table structure in H2 test-database as it is in our Oracle dev-database.

The Oracle SQL statements include MERGE statements and use alias for table names and USING in the query.

How can I modify this query dynamically to be compatible with H2 in a way that it does not alter the existing query in the dev environment?

Example of the Oracle SQL, to be made compatible with H2,

MERGE INTO TABLE T1
USING ( SELECT ....
        ...........
        FROM DUAL) T2

(T1 & T2 are the alias for the table)

Answer

Thomas Mueller picture Thomas Mueller · Dec 5, 2012

The MERGE statement in H2 has a slightly different, simpler syntax:

MERGE INTO TEST(ID, NAME) KEY(ID)
SELECT 1, 'Hello' FROM DUAL

I guess you would have to write two statements, one for H2, and one for Oracle. The SELECT part would be the same however. The Oracle MERGE statement would be longer, I believe it would be:

MERGE INTO TEST T
USING (SELECT 1 ID, 'Hello' NAME FROM DUAL) D
ON (T.ID = D.ID)
WHEN MATCHED THEN 
UPDATE SET T.NAME = D.NAME
WHEN NOT MATCHED THEN 
INSERT (B.ID, B.NAME) VALUES (D.ID, D.NAME);

Related questions

Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.) I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting …

Read More
How to find rows in one table that have no corresponding row in another table

I have a 1:1 relationship between two tables. I want to find all the rows in table A that don't have a corresponding row in table B. I use this query: SELECT id FROM tableA WHERE id NOT IN (SELECT id …

Read More
insert a BLOB via a sql script?

I have an H2 database (http://www.h2database.com) and I'd like to insert a file into a BLOB field via a plain simple sql script (to populate a test database for instance). I know how to do that …

Read More