Can I rename the values in a MySQL ENUM column in one query? Suppose I have a Database table with an ENUM('value_one','value_two').
I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:

ALTER TABLE `table` MODIFY `column` ENUM('value_one','value_two','First value','Second value');
UPDATE `table` SET `column`='First Value' WHERE `column`='value_one';
UPDATE `table` SET `column`='Second Value' WHERE `column`='value_two';
ALTER TABLE `table` MODIFY `column` ENUM('First value','Second value');


Is there a more efficient way to do this, E.G. a way to accomplish this with a  single ALTER TABLE statement?
The follolwing technique I am about to show you will require guts of steel.

Given the following criteria


datadir is /var/lib/mysql
table is mydb.mytb
enum column called is called enum_col
engine is MyISAM


Here is a death-defying crack at it:


CREATE TABLE mydb.mybt LIKE mydb.mytb;
ALTER TABLE mydb.mybt MODIFY enum_col ENUM('First value','Second value');
SET wait_timeout=86400; SET interactive_timeout=86400;
FLUSH TABLES WITH READ LOCK;
In a separate OS/SSH Session, swap the .frm files


$ mv /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/myxx.frm
$ mv /var/lib/mysql/mydb/mybt.frm /var/lib/mysql/mydb/mytb.frm
$ mv /var/lib/mysql/mydb/myxx.frm /var/lib/mysql/mydb/mybt.frm

UNLOCK TABLES;
DROP TABLE mydb.mybt;


That's it !!!

CAVEAT : I CANNOT TAKE CREDIT FOR THIS !

This technique comes from "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:


  The technique we are about to demonstrate is unsupported,
  undocumented, and may not work. Use it at your risk. We advise you to
  back up you data first!


Give it a try ! (Please let us know how it turned out)

UPDATE 2011-10-05 17:49 EDT

If the table is MyISAM and you have enough space in production and a straight-up downtime window, try this:


service mysql restart --skip-networking
In a separate OS/SSH Session, make a copy of the table


cp /var/lib/mysql/mydb/mytb.frm /var/lib/mysql/mydb/mytbplay.frm
cp /var/lib/mysql/mydb/mytb.MYD /var/lib/mysql/mydb/mytbplay.MYD
cp /var/lib/mysql/mydb/mytb.MYI /var/lib/mysql/mydb/mytbplay.MYI



INFORMATION_SCHEMA.TABLES will automatically detect the presence of the new table called mydb.mytbplay.


Perform the guts-of-steel algorithm on mydb.mytbplay
You test the integrity of mydb.mytbplay


If you are satisfied


ALTER TABLE mydb.mytb RENAME mydb.mytb_backup;
ALTER TABLE mydb.mytbplay RENAME mydb.mytb;
service mysql restart


Give it a try!