关于H2中的重复密钥更新功能 [英] On duplicate key update feature in H2

问题描述

我已经使用H2(嵌入式)开发了Java桌面应用程序.我只是具有有关数据库的基本知识，因此我只安装了H2并创建了一个架构名称RecordAutomation，然后将表添加到该架构中.现在，我正在尝试对无法使用的特定表使用ON DUPLICATE KEY UPDATE功能，从而导致sql语法错误，我检查我的查询，发现它正确无误，如下所示:

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

推荐答案

您需要使用MySQL模式.为此，请将;mode=MySQL附加到数据库URL. (此功能尚未正确记录).

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;

这篇关于关于H2中的重复密钥更新功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！