Oracle MERGE statement in H2 database

Question

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)

Your project has chosen a different flavour of database, one with a different SQL drammar, for automated testing? Fnord. If you have to rewrite your application so that your automated tests runs what have your tests proved? — APC, Dec 05 '12 at 04:49
we choose H2, because it can run a in-memory database which is very fast. — user1877775, Dec 05 '12 at 05:12
Yes but if it can't run the SQL grammar you use in Dev and Production it doesn't matter how fast the tests run, they're irrelevant. — APC, Dec 05 '12 at 23:50
I see no point why this question is downvoted - it IS the feature of H2 to emulate Oracle statement syntax ('MODE=Oracle') and it IS a common and good practice in industry to run quick integration tests on in-memory databases. — Boris Treukhov, Jun 11 '14 at 13:52

Thomas Mueller · Answer 1 · 2012-12-05T05:52:50.283

12

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

edited Dec 05 '12 at 05:52

answered Dec 05 '12 at 05:44

Thomas Mueller

48,905
14
116
132

Thanks for the quick response. So, as per the syntax above does that mean, I would not be able to use an alias name for the query in H2? – user1877775 Dec 05 '12 at 06:35
Yes, but it's not the alias name. The whole syntax of the statement is different. – Thomas Mueller Dec 05 '12 at 07:44

score 9 · Answer 2 · answered Oct 25 '19 at 13:30

9

I am from 2019. H2 supports standard "MERGE INTO ... USING ... WHEN ...". Documentation

answered Oct 25 '19 at 13:30

Igor Mukhin

15,014
18
52
61

score 7 · Answer 3 · answered Jun 11 '14 at 14:37

Standard SQL merge syntax support is currently on the roadmap of H2.

Support standard MERGE statement: http://en.wikipedia.org/wiki/Merge_%28SQL%29

However, in some simplistic cases you can use INSERT ... SELECT + WHERE NOT EXISTS For example to insert only if the record does not exist

INSERT INTO T1(K1, V2, V3) 
SELECT 1, 2, 3 FROM DUAL
  WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE
K1 = 1 AND V2 = 2 AND V3 = 3);

This construction works both in Oracle and in H2 (at least in MODE=Oracle), so you don't have to have separate SQL inserts for tests and prod.

Oracle MERGE statement in H2 database

3 Answers3

Linked