Hi Evan,
That's an excellent answer. Can you post it here?
http://stackoverflow.com/questions/6306592/merge-into-table-containing-auto-increment-columns
More replies below.
On 14/06/2011 10:20 AM, Evan wrote:
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.
1. It's not obvious to me why H2 understands that when you search
for a null key you mean to insert the default value, but when you
specify omit "id" from VALUES it doesn't know to do the same.
2. On second glance, your solution doesn't actually work properly.
It always inserts a new row even if there is already a matching row with
the same [uuid, time] values.
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.
The MERGE syntax is formally specified by the SQL:2008 standard
(which is freely available online). I find it to be far less ambiguous
than H2's. If I understand them correctly, I should be able to:
MERGE INTO AUDIT_TRANSACTION_IDS
USING audit_transaction_ids ON audit_transaction_ids.uuid = TRANSACTION_ID()
WHEN NOT MATCHED THEN INSERT (uuid, time) VALUES (TRANSACTION_ID(), NOW())
That being said, I don't think I can use MERGE INTO for my purposes. It seems
that MERGE INTO does not return any values, so if there *is* a match there is
no way for me to get back the matching id. I'll need to use separate
SELECT/UPDATE statements to implement what I want.
PS: In case you are curious about the official MERGE INTO syntax, here
is a sniplet from the 2008 specification:
Function
Conditionally update rows of a table, or insert new rows into a table,
or both.
Format
<merge statement> ::=
MERGE INTO <target table> [ [ AS ] <merge correlation name> ]
USING <table reference>
ON <search condition> <merge operation specification>
<merge correlation name> ::=
<correlation name>
<merge operation specification> ::=
<merge when clause>...
<merge when clause> ::=
<merge when matched clause>
| <merge when not matched clause>
<merge when matched clause> ::=
WHEN MATCHED THEN <merge update specification>
<merge when not matched clause> ::=
WHEN NOT MATCHED THEN <merge insert specification>
<merge update specification> ::=
UPDATE SET <set clause list>
<merge insert specification> ::=
INSERT [ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
VALUES <merge insert value list>
<merge insert value list> ::=
<left paren>
<merge insert value element> [ { <comma> <merge insert value element> }... ]
<right paren>
<merge insert value element> ::=
<value expression>
| <contextually typed value specification>
Gili
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.
