Hi all,
I'm having a wee problem with a mysql query.
First a little background: there's a program that uses a table, let's call it BLAH. Now, blah's primary key wasn't set to be auto_increment from the beginning. Now I need to write a flat mysql query to make it so - I need it to be in a pretty flat sql file as we have to distribute this to other users of this program (traditionally any other updates have been in this flat form - that's why I'm reluctant to move away from this).
After much banging my head against a wall, I've figured out the basic query to do it.
alter table BLAH auto_increment=<$safe number>
Where $safe_number is one that doesn't conflict with any entries already in BLAH
After that, I do
alter table change BLAHID BLAHID ...details... auto_increment
And then auto_increment starts from $safe_number. Now, because this is going out to users that will have differently populated BLAH tables, I'd like to select the next free number.
Problem is, even though we're using mysql 5.0 I can't do anything like
set @safe_number=(select max(BLAHID) + 1 from BLAH) ;; This works
alter table BLAH auto_increment=@safe_number ;; this doesn't
Does anyone know of any workarounds to make it work like I intended without resorting to glue code?
Cheers,
Aoife
