MERGE statement

[satterlee]

I have a table with an identity column X. If I try and do a merge
based on some other unique column the merge fails with the error
Column X contains null values [90081-69]. An insert with the exact
same parameters works fine. Is this expected behavior?

Also, I have found that if I want to use updateRow or some other
resultSet update type method, I must have put the primary key in the
query that created the result set, even if I have no need for it.
This seems somewhat reasonable to need this info, but I see no
documentation with either h2 or java sql, that implies that this needs
to be done. Again, is this expected behavior?



Dave Satterlee

[satterlee]

I guess I didn't mention that the merge was for a non-existent row,
thus an insert, not an update.

Dave

[Thomas Mueller]

Hi,

> I have a table with an identity column X. If I try and do a merge
> based on some other unique column the merge fails with the error
> Column X contains null values [90081-69]. An insert with the exact
> same parameters works fine. Is this expected behavior?

Sorry I can not reproduce this problem. Could you post a simple,
standalone test case that reproduces the problem? It would be great if
the test case does not have any dependencies except the H2 jar file
(that is, a simple SQL script that can be run in the H2 Console, or a
Java class uses the JDBC API and is run using a static main method).
Please include any initialization code (CREATE TABLE, INSERT and so
on) in the Java class or in a .sql script file.

> Also, I have found that if I want to use updateRow or some other
> resultSet update type method, I must have put the primary key in the
> query that created the result set, even if I have no need for it.
> This seems somewhat reasonable to need this info, but I see no
> documentation with either h2 or java sql, that implies that this needs
> to be done. Again, is this expected behavior?

It is not documented very well. The only place where it appears is:
http://www.h2database.com/javadoc/org/h2/jdbc/JdbcResultSet.html - in
the class doc at the top "Result sets are updatable when the result
only contains columns from one table, and if it contains all columns
of a unique index (primary key or other) of this table."

I will try to improve this paragraph.

Regards,
Thomas