Alter a MySQL column to be AUTO_INCREMENT?

Question

I’m trying to modify a table to make its primary key column `AUTO_INCREMENT` after the fact. I have tried the following SQL, but got a syntax error notification. <!-- language: lang-sql --> ALTER TABLE document ALTER COLUMN document_id AUTO_INCREMENT Am I doing something wrong or is this not possible? <pre> +--------------------+ | VERSION() | +--------------------+ | 5.0.75-0ubuntu10.2 | +--------------------+ </pre>

Answer 1

Try this as a prepared statement:

SELECT @max := MAX(document_id)+ 1 FROM document;

PREPARE stmt FROM 'ALTER TABLE document AUTO_INCREMENT = ?';EXECUTE stmt USING @max;

DEALLOCATE PREPARE stmt;