H2 INSERT SELECT ON DUPLICATE KEY UPDATE throws "Unique index or primary key violation" error

Question

H2 (started with MODE=MYSQL on) supports INSERT ON DUPLICATE KEY UPDATE statement only with VALUES clause, while throws a "Unique index or primary key violation" error when using INSERT SELECT statement.

Here is an example:

-- creating a simple table
CREATE TABLE test_table1 (
  id INT NOT NULL,
  value VARCHAR(255) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

-- inserting a value
INSERT INTO test_table1
VALUES (1, 'test1');

-- trying to insert on duplicate key update: it works!
INSERT INTO test_table1
VALUES (1, 'test2')
ON DUPLICATE KEY UPDATE value='test2';

-- trying using INSERT SELECT: it throws Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.TEST_TABLE1(ID)"
INSERT INTO test_table1
SELECT 1, 'test2'
FROM test_table1
ON DUPLICATE KEY UPDATE value='test2';

I'm using H2 db version 1.4.192. Is it a bug? Or is there something wrong with my code?

Thank you

Answer 1

On you H2 console, if you have 'HIBERNATE_SEQUENCES' table make sure to check what is the NEXT_VAL for SEQUENCE_NAME = 'default'.

In my case, I had 2 row (insert statement) in my /src/main/resources/data.sql and the NEXT_VAL was 2 which was causing problems. I changed to 3 with update statement, and it now works fine.

Answer 2

Is there something wrong with my code?

Yes, there is. Why are you inserting into an auto-increment column? You should be specifying the columns with non-autogenerated data. So:

INSERT INTO test_table1(value)
    VALUES ('test1');

And:

INSERT INTO test_table1(value)
    SELECT 'test2'
    FROM test_table1
    ON DUPLICATE KEY UPDATE value = VALUES(value);

Your are getting the error because ON DUPLICATE KEY resets value, but that has nothing to do with the primary key on the table.