Create Sequence starting with maximum row count value of a table for HSQL

Asked Oct 29 '19 at 10:12

Active Oct 29 '19 at 10:28

Viewed 136 times

I am trying to create a sequence with maximum row count value of some table. So far I could only do this

create sequence "mysequence" start with 1;

Instead of starting with one, I need to start with max row count value of a table. How to accomplish this in HSQL

edited Oct 29 '19 at 10:28

asked Oct 29 '19 at 10:12

venkat

You will need dynamic SQL for that. HSQLDB does not allow the "start with" (not starts with) value to be a query (and neither does ALTER SEQUENCE). – Oct 29 '19 at 10:29
Can you please give that dynamic SQL solution? – venkat Oct 29 '19 at 10:31
You can find the max value and use the number in the CREATE SEQUENCE statement. – fredt Oct 29 '19 at 11:09
@fredt can you please post the dynamic sql for that? – venkat Oct 29 '19 at 11:31
You cannot do it with dynamic SQL in HSQLDB. – fredt Oct 29 '19 at 13:05
You can write a a few lines of Java code to SELECT the max value then generate the CREATE SEQUENCE string with the value and execute it. – fredt Oct 29 '19 at 13:07
I did it using java, thanks - @fredt – venkat Oct 30 '19 at 12:14

0 Answers0