How can I remove a value from an enum in mysql?

Question

I have got a table with an ENUM in it containing 3 values: one, two, three. The table is already containing data, but ENUMvalue two isn't used at the moment, so I want to delete it from the ENUM. The result would be an ENUM with only 2 values: one, three.

Can I just use an ALTER TABLE statement and say like:

ALTER TABLE 'tablename' CHANGE 'enumname' ENUM('one', 'three');

Answer 1

Try this :-

alter table tablename
change `columname`  `columname` enum ('one', 'three');

Its working fine.

Answer 2

Enums are saved as integers so all your rows with enum="one" is actually equal to 1, enum="two" is 2 and enum="tree" is 3.

Seems MySQL is doing all the magic to change those values if enum names match so you just need to make sure there are no rows with 'two'

http://sqlfiddle.com/#!2/ef76d/1

CREATE TABLE `tablename` (`name` VARCHAR(32), `enumname` ENUM('one', 'two', 'three'));
INSERT INTO `tablename` VALUES ('one', 'one'),('two', 'two'),('three', 'three');
DELETE FROM `tablename` WHERE `enumname`=2;
ALTER TABLE `tablename` MODIFY `enumname` ENUM('one', 'three');