Hi Thomas,
Like this:
create table employee (id identity not null, email varchar(100) not null);
insert into employee (email) values ('[email protected]');
If the row does not exist, I'd retrieve the inserted primary key. If it
does exist, I'd want to (but don't know how) retrieve the existing primary
key. I ended up implementing this using two separate statements (insert
followed by select) but I was wondering if there was a better way.
Thanks,
Gili
On Sunday, September 22, 2013 8:09:45 AM UTC-4, Thomas Mueller wrote:
> 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]<javascript:>
> > 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] <javascript:>.
>>
>> To post to this group, send email to [email protected]<javascript:>
>> .
>> 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.
