Pass SELECT MAX(`Id`) FROM Table to setval()

Question

I want to pass (SELECT MAX(Id) FROM Table to mariadb's setval() function I tried with:

SELECT setval(`MySequence`, (SELECT MAX(`Id`) FROM `Table`));

but it doesn't work, I also tried:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
SELECT setval(`MySequence`, @max_value);

how am I supposed to do this?

EDIT I made a mistake posting the question. I was using SET on the second code and is not working

EDIT As I said on the comments I'm trying to do this just once, executing from an Entity Framework Core migration. What I ended doing is executing the SELECT MAX(Id) FROM Table and recovering that value from the migration code to interpolate it later on the $"SELECT setval('sequence', {value}, true)"

Answer 1

I found working workaround using prepared statement:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
EXECUTE IMMEDIATE CONCAT('SELECT SETVAL(`MySequence`, ', @max_value, ')');

Answer 2

In a stand-alone statement (not a query), SET is generally used to assign value to a user-defined variable. Try the following instead:

SET @max_value = (SELECT MAX(`Id`) FROM `Table`);
SELECT setval(`MySequence`, @max_value);

Answer 3

In a select, use := to assign variables:

SELECT @max_value := MAX(`Id`) FROM `Table`;
SELECT setval(`MySequence`, @max_value);

You might want to add 1 to the value.

I think you can do:

SELECT setval(`MySequence`, MAX(Id))
FROM `Table`;

Answer 4

It seems that is not possible to do this as of MariaDB 10.3.16

I've raised a bug on https://jira.mariadb.org/browse/MDEV-20111 for devs to consider adding this feature or upgrading the documentation to make explicit that it can't be done.

I worked arround this by selecting the value using the Mysqlconnector in c# code

private long ReadMaxIdFromTable()
{

    MySqlConnection connection = null;
    try
    {
        var environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");



        var builder = new ConfigurationBuilder();
        var builderenv = builder.AddJsonFile("config/appsettings.json", optional: false, reloadOnChange: false)
                         .AddJsonFile($"config/appsettings.{environment}.json", false, false).AddEnvironmentVariables();

        IConfigurationRoot configuration = builderenv.Build();
        var connectionString = configuration.GetConnectionString("ConnectionStringName");

        connection = new MySqlConnection(connectionString);
        connection.Open();
        var cmd = connection.CreateCommand() as MySqlCommand;
        cmd.CommandText = @"SELECT MAX(`Id`) FROM `Table`";
        var result = (long)cmd.ExecuteScalar();
        return result;
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        if (connection != null && connection.State != System.Data.ConnectionState.Closed)
        {
            connection.Close();
        }
    }
}

Is not as clean as I would like it but, it gets the job done.

Later I use SETVAL interpolating the value in the sql string again from c# code.

 var currentSeq = ReadMaxIdFromTable();
 migrationBuilder.Sql($"SELECT SETVAL(`MySequence`, {currentSeq}, true);");

Also, beware that all the Up() code it's executed BEFORE anything gets to the database, this means that SELECT MAX(Id) FROM Table; has to result in the value we're looking for before the migration starts manipulating the database.