Set generator to value of another generator

I want to set the value of the generator Dest to the actual value of generator Src:

ALTER SEQUENCE Dest RESTART WITH GEN_ID(Src, 0);

This doesn't work:

Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown

• line 1, column 39. GEN_ID. ---------------------------------- SQLCODE: -104 SQLSTATE: 42000 GDSCODE: 335544569

How to do that?

You can't do that with ALTER SEQUENCE, because like any other DDL, it only accepts literals, not expressions.

There are two methods you can use:

1. Use GEN_ID to modify the generator to the desired value:

   select gen_id(dest, gen_id(src, 0) - gen_id(dest, 0))
   from RDB$DATABASE
   

   This will change the value of dest by the difference between the current values of src and dest, which will make dest equal to src (assuming there are no other changes to either dest or src concurrently).

2. Use PSQL EXECUTE STATEMENT (e.g. in an EXECUTE BLOCK) to execute the ALTER SEQUENCE; although not recommended, you can execute DDL that way with dynamic values. For example:

   execute block
   as
   begin
      execute statement 'alter sequence dest restart with ' || gen_id(src, 0);
   end
   

   NOTE: With Firebird 4 or higher, you need to use (gen_id(src, 0) + 1) (if the sequence has a different increment, use that value instead of 1), as RESTART WITH was changed to conform to the SQL standard to set the sequence so the next value generated with NEXT VALUE FOR is the restart-with value, instead of setting the sequence equal to the restart-with value.