H2 gives syntax error for SQL merge or update query copied from official H2 site

Question

I want to run an upsert query on H2 databese, so WHEN MATCHED then update WHEN NOT MATCHED than insert.

I have table: create TABLE TESTTABLE (NAME VARCHAR2(100) NOT NULL, NUMBER1 INT, NUMBER2 INT, );

And 1 row in it: Peter 1 2

I tried these copied code from official H2 site:

MERGE INTO TESTTABLE AS T USING DUAL ON NAME = 'Peter'
    WHEN NOT MATCHED THEN INSERT VALUES ('Peter3', 1, 2)
    WHEN MATCHED THEN UPDATE SET NUMBER1 = 2 and NUMBER2 = 3;

Error: Syntax error in SQL statement "MERGE INTO TESTTABLE AS[*] T USING ...

Without the AS:

MERGE INTO TESTTABLE USING DUAL ON NAME = 'Peter'
    WHEN NOT MATCHED THEN INSERT VALUES ('Peter3', 1, 2)
    WHEN MATCHED THEN UPDATE SET NUMBER1 = 2 and NUMBER2 = 3

Error: Syntax error in SQL statement "MERGE INTO TESTTABLE USING[*] DUAL ON NAME = 'Peter'...

Other try:

MERGE INTO TESTTABLE (NAME, NUMBER1, NUMBER2)
    KEY(NAME) VALUES('PETER')
    WHEN MATCHED THEN UPDATE SET NUMBER1 = 2, NUMBER2 = 3
    WHEN NOT MATCHED THEN INSERT VALUES ('Peter3', 1, 2);

Error: Syntax error in SQL statement "MERGE INTO TESTTABLE (NAME, NUMBER1, NUMBER2) KEY(NAME) VALUES('PETER') WHEN[*] MATCHED THEN";

It seems none of code is right on h2database.com. Any idea? Thanks for the help!

Evgenij Ryazanov Evgenij Ryazanov · Accepted Answer · 2019-07-18T13:31:55

The documentation on website is for the latest version of H2, currently it is 1.4.199.

Your error message definitely came from some old unsupported version that does not have the standard MERGE statement. You need to upgrade your version of H2 to the latest one.

Note that MERGE INTO TESTTABLE (NAME, NUMBER1, NUMBER2) KEY(NAME) VALUES('PETER') WHEN MATCHED … is not valid at all. There are two different incompatible MERGE statements in H2. The H2-specific one that has KEY clause and VALUES clause (or a subquery) and the standard one that has additional source table, ON condition, and WHEN MATCHED / WHEN NOT MATCHED clauses. Both commands are separately described in the documentation. You can't mix them.

H2 gives syntax error for SQL merge or update query copied from official H2 site

1 Answers