Hi, > How can we insert a new row unless one already exists, and in either case return the primary key of the inserted/matched row?
Well, how could you insert the row if you don't know the primary key already when inserting? Regards, Thomas On Mon, Sep 16, 2013 at 4:38 AM, Gili <[email protected]> wrote: > I'm also curious about this: > > How can we insert a new row unless one already exists, and in either case > return the primary key of the inserted/matched row? > > Thanks, > Gili > > > On Thursday, October 25, 2012 4:31:19 PM UTC-4, Brent Ellwein wrote: > >> OK, so how would the return case look? I assume that you mean I would >> set-up a pre-insert trigger which would do *something* before the insert >> actually happens and detect that the insert isn't necessary and then return >> a resulting value. Is that essentially what you are suggesting? >> >> I guess that I don't see how I'm going to get the ID back to the client >> using this mechanism. Can you provide an example? >> >> Thanks! >> >> >> On Thu, Oct 25, 2012 at 11:16 AM, Thomas Mueller >> <[email protected]>wrote: >> >>> Hi, >>> >>> > BUT what I would like to do is write a stored procedure which does it >>> all at once and then returns the id value quickly >>> >>> Yes, you could use a user defined Java function >>> >>> > However, I cannot seem to cache the prepared statement in my function >>> call >>> >>> Is it *really* a big problem in this case? If yes, a workaround would be >>> to use a trigger, as within a trigger you can cache prepared statements >>> (see the class FullText for details how this can be done). In your case, it >>> would be a 'instead of insert' trigger I believe. Maybe even easier might >>> be a trigger on update (you wouldn't need to use the JDBC API in this case >>> I believe). >>> >>> Regards, >>> Thomas >>> >>> >>> >>> On Tue, Oct 23, 2012 at 7:04 AM, [email protected] >>> <[email protected]>wrote: >>> >>>> >>>> On Tue, Oct 23, 2012 at 3:10 AM, <[email protected]> wrote: >>>> >>>>> merge into File (filepath, filesize, datemodified) key (filepath) >>>>> values (?,?,?) >>>> >>>> >>>> I've just created a table and tried this from the H2 console >>>> >>>> merge into File (filepath, filesize, datemodified) key (filepath) >>>> values ('foo',9999,889) >>>> >>>> No exception. And I run it with more than once. So, I have no idea why >>>> you are getting one. >>>> >>>> >>>> -- >>>> Vasile Rotaru >>>> >>>> -- >>>> 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 h2-database...@** >>>> googlegroups.com. >>>> >>>> For more options, visit this group at http://groups.google.com/** >>>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>>> . >>>> >>> >>> -- >>> 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 h2-database...@** >>> googlegroups.com. >>> >>> For more options, visit this group at http://groups.google.com/** >>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>> . >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
