sql - Set generator to value of another generator - Stack Overflow

admin管理员组

文章数量:1122826

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?

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?

• sql
• firebird
• firebird-3.0

Share Improve this question Follow edited Nov 22, 2024 at 7:54 Mark Rotteveel 109k224224 gold badges155155 silver badges218218 bronze badges asked Nov 21, 2024 at 18:26 TomCat500TomCat500 17444 silver badges1414 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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.

本文标签： sqlSet generator to value of another generatorStack Overflow