Search code examples
mysqlauto-increment

Mysql: change a column to auto_increment with subquery to get max column id

I just started working on mysql and have a question.. So I have a table tests that has columns like this test_id test_description test_results

where test_id is BIGINT UNSIGNED NOT NULL;

test_id column looks like

1
2
3
123
321
1293
1299
1300
1301

I'm trying to modify test_id, rename it, and set it auto increment based on the largest number in the column.

I could do this

ALTER TABLE tests CHANGE test_id result_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT=1302

So this would work, however, I want to set the auto increment to a variable, based on the largest number of test_id column. I tried this

ALTER TABLE tests CHANGE test_id result_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT=(SELECT MAX(test_id) AS test_id FROM tests);

It is complaining about syntax errors around AUTO_INCREMENT.

Any idea if there's a way to do this?

Much appreciated

EDIT: JStephen's comment! I think it's working now. TY guys.

Solution

  • If you do not specify the auto increment number it will automatically use the highest value when determining what the next increment will be.