title: Merge in H2 database
body: <p>I am using H2 database's <code>MERGE</code> command but don't understand a part of its <a href="http://www.h2database.com/html/grammar.html#merge" rel="nofollow noreferrer">documentation</a>. The troubling part being -</p>
  
  <blockquote>
    <p>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</p>
  </blockquote>
  
  <p>I executed the following SQL statements to try to understand it</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>The table looks like this after the above statements</p>
  
  <pre><code>+---------+
  | ID  INT |
  +---------+
  | 1   42  |
  | 2   47  |
  | 3   44  |
  | 4   45  |
  | 5   46  |
  | 6   48  |
  +---------+
  </code></pre>
  
  <p>So no matter whether I include the primary key in the <code>MERGE</code>, it is getting incremented if the statement results in a record being added &amp; leaves the already existing value untouched in case the statement results in a record being updated. </p>
  
  <p>It would be great if somebody can shed light on what the quoted part of the documentation is trying to express</p>