Menu
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)
630
MERGE USINGUpdates or deletes existing rows, and insert rows that don't exist. The ON clause specifies the matching column expression. Different rows from a source table may not match with the same target row (this is not ensured by H2 if target table is an updatable view).
An Oracle MERGE statement is used to pull data from the source table(s) and update or insert into the target table based on condition. Merge statement allows us to make condition-based insert or update into a target table. It is introduced in Oracle 9i version and it supports 9i or later version.
The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.
Syntax. Following is the basic syntax of INSERT INTO statement. INSERT INTO tableName { [ ( columnName [,...] ) ] { VALUES { ( { DEFAULT | expression } [,...] ) }
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
