Don't know if any DBs support this; indeed only insert statements return the autogenerated primary key.
Anyway, I'm able to work around this. But being able to get affected keys from merge and update operations would be an useful feature I think. For example, http://stackoverflow.com/questions/1246361/getting-the-primary-key-value-after-a-merge-command http://www.codingforums.com/showthread.php?t=192983 Best Regards, Joonas On Aug 6, 12:59 pm, Thomas Mueller <[email protected]> wrote: > Hi, > > I'm sorry, I don't know about a solution, except running separate > statements. I also don't know of a way to get the primary keys for a > simple update statement. Do other databases support such a feature? > > Regards, > Thomas > > > > On Thursday, August 5, 2010, Joonas Pulakka <[email protected]> wrote: > > Ok, given that the "de facto" default starting point is 1, the zero > > return value as a flag makes sense. > > > Still, if the generated key is 0, meaning that an existing row was > > updated, I need to get primary key for that row somehow. Is there any > > other way to do it than use something like "SELECT primarykey FROM > > table WHERE keyColumnNameUsedInMergeStatement IS > > keyColumnValueUsedInMergeStatement" and hope that no one has meanwhile > > edited that value? That's pretty ugly, for sure... > > > Best Regards, > > Joonas > > > On Aug 5, 11:12 am, Kerry Sainsbury <[email protected]> wrote: > >> To answer the second part of your question, IDENTITY columns do default to > >> start at 1 and increment by 1. > > >> I notice that it doesn't seem to be documented anywhere, but you actually > >> define the start and increment values when you define the IDENTITY column, > >> like this: > > >> create table blah(id identity(7,2), val varchar(10)); > > >> insert into blah(val) values ('X') > >> insert into blah(val) values ('Y') > > >> select * from blah > > >> ... and the results will be: > > >> id val > >> 7 X > >> 9 Y > > >> So as long as you didn't use a negative starting point you should be OK :-) > > >> Cheers > >> Kerry > > >> On Thu, Aug 5, 2010 at 7:47 PM, Joonas Pulakka > >> <[email protected]>wrote: > > >> > I need to get the primary key for the affected row after performing a > >> > MERGE statement, regardless of whether the row got inserted or > >> > updated. Is there a standard way to do that? Using the generated key > >> > from the statement was my first idea, but it doesn't work quite that > >> > way: > > >> >http://www.h2database.com/html/grammar.html#mergesaysthat "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." > > >> >http://www.h2database.com/html/datatypes.html#identity_typesaysthat > >> > "Possible values: -9223372036854775808 to 9223372036854775807" > > >> > So, if 0 is a valid value for an auto-incremented primary key, how am > >> > I supposed to know from the generated key value whether a new row with > >> > primary key 0 was just inserted (in which case I already have the > >> > primary key), or whether a row with some other primary key was updated? > > >> > -- > >> > 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]<h2-database%2bunsubscr...@googlegr > >> > oups.com> > >> > . > >> > 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 > > athttp://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.
