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#mergesays that "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_typesays that > > "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 at http://groups.google.com/group/h2-database?hl=en.
