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!