And I'd say that you may do this without doing 'Set generator to 0' (I
think Firebird used to update a counter that had a very low maximum
value before a backup/restore was necessary, so I remember a trick for
doing the same thing only using GEN_ID. I doubt it is still required
to do things this way).

Let's see if this can be done in one statement if we also want to set
the generator to the next million:

Select GEN_ID(GEN_Name,(((Select max(ID)/1000000)+1)*1000000 from
TABLE) - GEN_ID(GEN_Name, 0)) from rdb$Database

Hmm, simpler than I thought it would be. Though I haven't actually
tried the statement.

Set

--- In firebird-support@yahoogroups.com, The Wogster wrote:
> Graeme Edwards wrote:
> >> Hi,
> >
> >> I am trying to set some generator values using a SELECT MAX(ID)
> >> FROM MYTABLE?
> >> Can this be done? or some automatic way for this?
> >
> >> I was thinking of some code like this:
> >> SET GENERATOR GEN_MY_TABLE_ID TO SELECT MAX(ID) FROM MYTABLE
> >
> > I'm making no comment on the advisability of doing this, but I
> > have had to do similar things when adding a new generator to an
> > existing table.
> >
> > One thing I have tried is
> >
> > Set Generator to 0
> >
> > Select Gen_ID(GEN_Name,(Select max(ID) from TABLE)) from
> > rdb$Database
> >
> > The select statement forces the update of the generator value by
> > Max(ID) from table. RDB$Database is used because it has one record
> > and I only want to increment the generator once by max(ID)
> >
> > Graeme
>
> I suggest adding a buffer value, say round UP to the next 1,000,000
> this will give you a definite starting point for the generator
> values, versus the previous values. It also means that if the old
> method of adding new records doesn't get disabled right away, that
> you don't get duplicate values.
>
> W