0

I have the following code in Maria DB ,

I want to use my function in a query to create a sequence that starts with the count(*) +1 of a my TABLE1

It gives me an error in the CREATE SEQUENCE query :

    CREATE FUNCTION myFuntion() RETURNS INT
    BEGIN
       DECLARE lastID INT DEFAULT 1;
       SELECT COUNT(*) INTO lastID FROM TABLE1;
       RETURN lastID+1;
    END;
    
   CREATE SEQUENCE seq101 START WITH myFuntion() INCREMENT BY 1 ;

Error :

MySqlError { ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'myFuntion() INCREMENT BY 1' }

1
  • START value must be numeric literal
    – Akina
    Commented Feb 13, 2023 at 16:50

1 Answer 1

1

Plan A Switch to AUTO_INCREMENT That is, simply an ALTER TABLE to change the column to AUTO_INCREMENT. It will automatically do the MAX(id)+1 to get started.

Plan B Using SEQUENCE:

  • The syntax error is because it is expecting a constant there. A workaround is to build a Stored Procedure to compute the number, construct a string for the CREATE statement, then PREPARE and EXECUTE it.

  • Caution: Without START TRANSACTION ... COMMIT, a separate connection could grab the "next" id before you can.

  • Change from COUNT(*)+1 to MAX(id)+1, just in case there are already gaps in the ids -- due to DELETEs or other operations that could create gaps. (Eg, INSERT IGNORE.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.