I really don't see the problem here.. Been using MERGE with auto_increment columns, never a problem (except for constraint errors, mine of course).
instead of this: MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES (TRANSACTION_ID(), NOW()) do this: MERGE INTO audit_transaction_ids (id, uuid, time) KEY(id) SELECT null, TRANSACTION_ID(), NOW() there goes your problem. your problem is, u set the key to be [id] but don't provide [id] itself in columns to be merged then how would you expect the merge to be done? it supposed to check [id] column from the values you provided, and when null, insert null or if exists, default(which is [id]'s NEXT_VAL) Cheers, Evan On Jun 9, 9:45 am, cowwoc <[email protected]> wrote: > Hi, > > When I execute: > > MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES > (TRANSACTION_ID(), NOW()) > > on the following schema: > > CREATE TABLE audit_transaction_ids (id IDENTITY PRIMARY KEY, uuid > VARCHAR UNIQUE NOT NULL, `time` TIMESTAMP NOT NULL); > > I get this error: > > org.h2.jdbc.JdbcSQLException: Column "ID" contains null values; SQL > statement: > MERGE INTO audit_transaction_ids (uuid, time) KEY(id) VALUES > (TRANSACTION_ID(), NOW()) [90081-155] > > In the above case the MERGE operation should always insert. Granted I > can (and will) replace it with an INSERT statement but I'm curious: > why is this failing? What is the correct way of invoking MERGE INTO > for a table containing an IDENTITY or AUTO_INCREMENT column? > > Thanks, > Gili -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
