MySQL Change a column ENUM value

Question

I have a MySQL table "content" which has a column page_type of type ENUM. The ENUM values are NEWS & PRESS_RELEASE. I need to replace NEWS with FEATURED_COVERAGE:

ALTER TABLE `content` CHANGE `pagetype` `pagetype` ENUM('FEATURED_COVERAGE','PRESS_RELEASE') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; 

But now the records in the table, which earlier had page_type NEWS are now empty, and there is no way that I can identify which records are NEWS, so that I can rename those to FEATURED_COVERAGE.

How to resolve such issues?

Answer 1

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; 

Answer 2

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