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,

  1. 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;
    
  2. Set the old enum value to new value for all records.

    UPDATE `content` set `pagetype` = 'FEATURED_COVERAGE' where
    `pagetype` = 'NEWS';
    
  3. 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