Good day,
On Jun 15, 2:22 am, cowwoc <[email protected]> wrote:
> Hi Evan,
>
> That's an excellent answer. Can you post it
> here?http://stackoverflow.com/questions/6306592/merge-into-table-containin...
>
> More replies below.
>
> On 14/06/2011 10:20 AM, Evan wrote:
>
I'm sorry, I did look into when Thomas pointed it out,
but since I haven't registered yet, I replied here.
>
>
>
>
> > 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.
>
What? I thought you said my answer was excellent. ?
I think I get what you're trying to do here.
if it has the same [uuid, time] then you want to update the record.
otherwise, insert.
Am I right?
Then you miss the MERGE point altogether.
try this.
MERGE INTO audit_transaction_ids (uuid, time) KEY (uuid, time)
VALUES (TRANSACTION_ID(), NOW())
*little performance tip: make sure you have uuid indexed.*
Now, according to the docs (Haven't confirm yet)
If the table contains an auto-incremented key or identity column, and
the row was updated, the generated key is set to 0; otherwise it is
set to the new key.
So observe the jdbc driver's Statement.getGeneratedKeys ResultSet and
you should be fine.
>
>
> > 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
Well anything that floats your boat.
I stated too, that I liked H2's implementation,
but it wouldn't hurt too much having a less ambiguous syntax,
especially cross-vendor one.
Cheers,
evan
--
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.
