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#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. > > -- 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.
