Damn my post gets eaten.. 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: Sorry haven't registered there. That's why I replied here. > > 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? Didn't you said my answer was excellent? I think I get what you're trying to do here. You want to update if it founds the same [uuid, time], insert if otherwise. Then try this. MERGE INTO AUDIT_TRANSACTION_IDS (uuid, time) KEY (uuid, time) VALUES (TRANSACTION_ID(), NOW()); *little performance tip: make sure uuid is indexed* as the docs said, 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. > 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 > Whatever floats your boat, man. I've stated before that I liked H2's syntax, but I wouldn't mind a more standardized, concise syntax, especially if it means better(and more) vendor-compatibility. 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.
