MySQL Change a column ENUM value
If I understand your question, you want to rename the existing enum value NEWS
to FEATURED_COVERAGE
. If so, you need to follow below steps,
-
Alter the table and add the new enum value to the column, so that you will have 3 enums
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE', 'NEWS') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
-
Set the old enum value to new value for all records.
UPDATE `content` set `pagetype` = 'FEATURED_COVERAGE' where `pagetype` = 'NEWS';
-
Alter the table and drop the old enum value.
ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
MySQL's enum always has a hidden option which is 0 as integer and '' as string. When you try to assign an invalid value it uses the hidden one.
Assuming all your empty values were 'NEWS' before the update you can change them with
UPDATE content SET pagetype = 'FEATURED_COVERAGE' WHERE pagetype = 0
I think default might have helped.
ALTER TABLE `content`
CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE')
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULt 'FEATURED_COVERAGE';
Now you have to blindly to an update
And never use an enum column if your value set is changing.
Since you have changed only one enum it will not be difficult for you
Try using this
UPDATE content SET pagetype = 'FEATURED_COVERAGE' WHERE pagetype = 0