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?

Accepted Answer

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 Change a column ENUM value

Solution

Problem