First things first, MERGE isn't like your ordinary SQL, it is basically a UPDATE which INSERTs when no record found to be updated.
wikipedia gives a more concise, standardized syntax of MERGE http://en.wikipedia.org/wiki/Merge_(SQL) but you have to supply your own update and insert. (whether this will be supported in H2 or not is not mine to answer) So how do you update a record using MERGE in H2? You define a key to be looked up for, if it is found you update the row (with column names you supply, and you can define DEFAULT here, to reset your columns to its defaults), otherwise you insert the row. Now what is Null? Null means unknown, not found, undefined, anything which is not what you're looking for. That is why Null works as key to be looked up for. Because it means the record is not found. MERGE INTO table1 (id, col1, col2) KEY(id) VALUES (Null, 1, 2) Null has a value. it IS a value. Now let's see your second SQL. MERGE INTO table1 (id, col1, col2) KEY(id) VALUES (DEFAULT, 1, 2) What is that implying? To me, it says I have this [default, 1, 2], ___find me a default in column id___, then update col1 to 1, col2 to 2, if found. otherwise, insert default to id, 1 to col1, 2 to col2. See what I underlined there? what does that even mean? what is default? how do you compare _default_ to id? Null is a SQL-standard value, DEFAULT is just a keyword. You can do stuff like, MERGE INTO table1 (id, col1, timeStampCol) KEY(id) VALUES (Null, 1, DEFAULT) but don't put DEFAULT in the key column.... It's just.. I don't know.. doesn't make any sense, at least to me. Hope this clears things up a bit. One final note, IMO H2's merge statement is simple and to the point, but as more RDBMS supports merge, I believe a more concise, less ambigue syntax is preferable to minimize vendor's implementation differences. Cheers, Evan On Jun 14, 10:28 pm, cowwoc <[email protected]> wrote: > 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 tohttp://www.h2database.com/html/grammar.htmlI 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.
