Alter Auto_increment Value By Select Result

Basically what I want is a working-version of the following code: ALTER TABLE table_name AUTO_INCREMENT = ( SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.

Solution 1:

Try this:

SELECT `AUTO_INCREMENT` INTO@AutoIncFROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA ='database_name'AND TABLE_NAME ='another_table_name';

SET@s:=CONCAT('ALTER TABLE `database_name`.`table_name` AUTO_INCREMENT=', @AutoInc);
PREPARE stmt FROM@s;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;

Solution 2:

Just upon quick glance, the error says it all. Syntax is incorrect

The syntax should adhere to:

ALTERTABLE table_name AUTO_INCREMENT=<INTEGER_VALUE>;

So looking at your query, remove the word "SET"

ALTERTABLE table_name AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA ='database_name'AND TABLE_NAME ='another_table_name'
);

Solution 3:

What about

ALTERTABLE table_name 
SET AUTO_INCREMENT = (SELECTMAX(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want the current increment value or

ALTERTABLE table_name 
SET AUTO_INCREMENT = (SELECTMIN(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want the same initial INCREMENT VALUE