Hi,
You would need a unique index on the column "email". For H2, you could
use a combination of:
insert into employee(email) select ? from dual where not exists(select
* from employee where email=?);
select id from employee where email=?
This could be written as a user defined function. This should work for
other databases as well. For H2, you could use "merge into
employee(email) key(email) values(?)" as well.
Regards,
Thomas
Hi Thomas,
Like this:
create table employee (id identity not null, email varchar(100) not
null);
insert into employee (email) values ('[email protected] <mailto:[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]> 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
[email protected].
For more options, visit this group at
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
[email protected].
For more options, visit this group at
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]
<mailto:h2-database%[email protected]>.
To post to this group, send email to [email protected]
<mailto:[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 a topic in the
Google Groups "H2 Database" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/h2-database/ejdytiTCvbA/unsubscribe.
To unsubscribe from this group and all its topics, 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.
