Change Sequence value with subselect

Question

I have the following case: I need to change the value of some sequences according to the max(id) of the table, I am trying to do something like:

ALTER SEQUENCE CFOP_SEQ INCREMENT BY (SELECT MAX(ID)+1 FROM CFOP);

But it returns me the message "Invalid number". Does anyone know how I can solve this problem ?

Answer 1

You can use a pl to do this. By storing the value of select within a variable using the into and then running the alter with the immediate execution.

declare
  maxId number;
begin 

  SELECT MAX(ID)+1 
    into maxId
  FROM CFOP;

  execute immediate 'ALTER SEQUENCE CFOP_SEQ INCREMENT BY '||maxId;
end;
/

If you need to write any other command you can add it in the PL/SQL body, in the case of SELECT you need to add the returns in variables or in a course using the INTO.

You only use the instant run Follow the link for better information about PL/SQL.

https://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm