将SQL Server查询转换为h2查询

Question

我一直在尝试在h2db中为集成测试用例编写以下SQL Server查询。

UPDATE
Test.Table1 WITH (UPDLOCK, SERIALIZABLE)
SET
field1 = :val1,
field6 = CURRENT_TIMESTAMP,
field4 = :val4,
field5 = :val5
WHERE
field2 = :val2
AND field3 = :val3;
IF @ @ROWCOUNT = 0 BEGIN
INSERT INTO
Test.Table1(
ID,
field1,
field2,
field3,
field4,
field5,
field6,
field7,
field8,
field9
)
VALUES
(
newid(),
:val1,
:val2,
:val3,
:val4,
:val5,
CURRENT_TIMESTAMP,
:val4,
:val5,
CURRENT_TIMESTAMP
);
END

我发现行计数在h2中无效。因此，尝试使用case命令编写，但似乎也不起作用。有人能帮忙吗？

Answer 1

您可以在SQL Server和H2:中使用相同的标准MERGE语句

MERGE INTO TEST.TABLE1
USING (SELECT :val1 V1, :val2 V2, :val3 V3, :val4 V4, :val5 V5) S
ON (FIELD2 = V2 AND FIELD3 = V3)
WHEN MATCHED THEN
UPDATE SET FIELD1 = V1, FIELD6 = CURRENT_TIMESTAMP, FIELD4 = V4, FIELD5 = V5
WHEN NOT MATCHED THEN
INSERT (ID, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD7, FIELD8, FIELD9)
VALUES (NEWID(), V1, V2, V3, V4, V5, CURRENT_TIMESTAMP, V4, V5, CURRENT_TIMESTAMP);

我假设您正在使用某种包装器来用实际值替换:val*。