About the duplicate key update feature in H2

Question

I developed an application for Java applications using H2 (Embedded). I just have basic knowledge of the database, so I just installed H2 and created the name of the RecordAutomation schema, and then added tables to this schema. Now I am trying to use the ON DUPLICATE KEY UPDATE function for a specific table that does not work, giving sql syntax error, I check my query, I found it correctly, 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 and try to solve this problem where it is discussed, since this function does not work for tables other than the default values. I have no idea about default and non-default. Please help me

Answer 1

You need to use MySQL mode. To do this, add ;mode=MySQL to the ;mode=MySQL URL. (This function has not yet been documented).

The table must have a primary key, or at least a unique index. Full 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;