0

I am using H2 database's MERGE command but don't understand a part of its documentation. The troubling part being -

If the table contains an auto-incremented key or identity column, and the row was updated, the generated key is set to 0; otherwise it is set to the new key

I executed the following SQL statements to try to understand it

CREATE TABLE h2_test (id INTEGER PRIMARY KEY AUTO_INCREMENT, int INTEGER)
INSERT INTO h2_test (int) VALUES (42)
INSERT INTO h2_test (int) VALUES (43)
INSERT INTO h2_test (int) VALUES (44)
MERGE INTO h2_test (int) KEY(int) VALUES (45)
MERGE INTO h2_test (id, int) VALUES (5, 46)
MERGE INTO h2_test (id, int) VALUES (2, 47)
INSERT INTO h2_test (int) VALUES (48)

The table looks like this after the above statements

+---------+
| ID  INT |
+---------+
| 1   42  |
| 2   47  |
| 3   44  |
| 4   45  |
| 5   46  |
| 6   48  |
+---------+

So no matter whether I include the primary key in the MERGE, it is getting incremented if the statement results in a record being added & leaves the already existing value untouched in case the statement results in a record being updated.

It would be great if somebody can shed light on what the quoted part of the documentation is trying to express

x-treme
  • 1,606
  • 2
  • 21
  • 39

1 Answers1

0

Working as expected. You have 7 INSERT/MERGE statements which results in 6 new records and one update. Your last MERGE statement updates the record that was created by the second INSERT statement (value 43). You typically use the KEY part of the MERGE to check if a row specied by a non-primary KEY exists. In your query, 45 does not exist yet. Add another column, then query for an existing "int" column value to see meaningful updates to existing rows.

Kalle
  • 2,157
  • 1
  • 22
  • 21