Remove Value from an Enum in MySQL

Removing Value from an enum in MySQL

We can use the ALTER command to remove a value from an ENUM in MySQL.

The ALTER database command lets you modify the type of a column, add or remove columns, or even modify ENUM values in a current database. You cannot delete a value from an ENUM type. To accomplish that task, you change the column definition - by changing the list of allowed values. This way, the structure of the table is not changed while the set of possible values for the ENUM column is changed.

Example

Let us first create a table of ENUM column called RANK with values as 'LOW', 'MEDIUM', and 'HIGH'.

CREATE TABLE DemoTable (
   Rank ENUM('LOW','MEDIUM','HIGH')
);

To verify the table structure use DESC command.

DESC DemoTable;

Following is the output of the above query ?

Following is the query to remove a value from an enum in MySQL using the ALTER TABLE command.

ALTER TABLE DemoTable CHANGE `Rank` `Rank` ENUM('LOW', 'HIGH');

In the above code, we used backticks (``), for RANK, because to avoid conflicts with both reserved keywords or special characters. For example suppose you wanted one column to be named as RANK, without backticks. MySQL will then interpret it as an SQL term.

Now let us check the description of the table once again ?

DESC DemoTable;

Following is the output of the above query ?

Conclusion

Removing a value from an ENUM column in MySQL involves altering the table's column definition using the ALTER TABLE command. By modifying the ENUM list, you can easily remove any unwanted values without affecting the data from the table. Always verify the table structure after making changes to ensure the new ENUM values are correctly added.