On duplicate key update feature in H2

Question

I have developed java desktop application with the use of H2(Embedded). I just have basic knowledge about database, so i simply installed H2 and create a schema name RecordAutomation and then add tables to that schema. Now i am trying to use the ON DUPLICATE KEY UPDATE feature for a specific table which is not working giving sql syntax error, i check my query i found it right, given below

INSERT INTO RECORDAUTOMATION.MREPORT 
(PRODUCTID ,DESCRIPTION ,QUANTITY ,SUBTOTAL ,PROFIT ) 
VALUES (22,olper,5,100,260) 
ON DUPLICATE KEY UPDATE SET QUANTITY = QUANTITY+5;

i search and try to solve this problem some where it is discussed like this feature does not work for non-default tables. i have no idea about default and non-default. please make help me

Answer 1

You need to use the MySQL mode. To do that, append ;mode=MySQL to the database URL. (This feature is not properly documented yet).

The table needs to have a primary key or at least a unique index. Complete example:

drop table MREPORT;

set mode MySQL;

create table MREPORT(PRODUCTID int primary key, 
DESCRIPTION varchar, QUANTITY int,  SUBTOTAL int, PROFIT int);

INSERT INTO MREPORT 
(PRODUCTID ,DESCRIPTION ,QUANTITY ,SUBTOTAL ,PROFIT ) 
VALUES (22,'olper',5,100,260) 
ON DUPLICATE KEY UPDATE QUANTITY = QUANTITY+5;