Evan,
I'm shocked that this works (I just confirmed it on my end). Why
does this work?
MERGE INTO AUDIT_TRANSACTION_IDS (id, uuid, time) KEY (id) SELECT null,
TRANSACTION_ID(), NOW();
but this fails?
MERGE INTO AUDIT_TRANSACTION_IDS (id, uuid, time) KEY (id)
VALUES(DEFAULT,TRANSACTION_ID(), NOW());
1. According to http://www.h2database.com/html/grammar.html I should be
able to use DEFAULT with a MERGE command. Is this a bug?
2. Why should use statement work while the other fails?
3. According to "Default expressions are used if no explicit value was
used when adding a row." It later on goes on to say "Identity and
auto-increment columns are columns with a sequence as the default."
which seems to contradict the behavior I'm seeing (omitting "id" gives
it a default value of null, not the default).
4. I don't fully understand which part of your MERGE command converts
null into a default value. Is the act of inserting null into an
AUTO_INCREMENT column converting it to DEFAULT? Or is it the act of
SELECTing null?
Thanks,
Gili
On 14/06/2011 1:59 AM, Evan wrote:
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.
