CODEWITHSUNDEEP
javamysqlhsqldb
Morten Berg
Morten Berg

Reputation: 1795

HSQLDBs 'ON DUPLICATE KEY UPDATE'-feature does not behave as in MYSQL?

I am using MYSQLs ON DUPLICATE KEY UPDATE-feature, and it works as I expect when running on a MYSQL database. But when I try to write tests running against a in-memory HSQLDB, I experience different behaviour.

Given the following table:

CREATE TABLE foo (id INT PRIMARY KEY NOT NULL, counter INT);

With the following insert statement:

INSERT INTO foo(id, counter) VALUES (1, 1) ON DUPLICATE KEY UPDATE counter=counter+1

Is this a bug, or have I misunderstood how ON DUPLICATE KEY UPDATE should work?

For a running example, see the following github-repository: https://github.com/mortenberg80/hsqldbtest

Upvotes: 1

Views: 217

Answers (1)

fredt
fredt

Reputation: 24372

The initial HSQLDB implementation of ON DUPLICATE UPDATE used the VALUES list as the source of the update in all cases where col_name was encounterd. It has been enhanced for version 2.5.1 to use the existing table row values as the source for col_name, and use the VALUES list as the source for VALUES(col_name). This corresponds with MySQL usage.

Upvotes: 2

Related Questions