CODEWITHSUNDEEP
sqlh2sql-merge
user1877775
user1877775

Reputation: 211

Oracle MERGE statement in H2 database

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)

Upvotes: 17

Views: 14652

Answers (3)

Igor Mukhin
Igor Mukhin

Reputation: 15368

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

Upvotes: 9

Boris Treukhov
Boris Treukhov

Reputation: 17774

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

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.

Upvotes: 7

Thomas Mueller
Thomas Mueller

Reputation: 50087

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

Upvotes: 12

Related Questions