Removing enum values ​​from mysql column

Question

I have a table with an enum column named action . Allowed values ​​are: act1,act2,act3,act4 . I want act3 and act4 be deleted, and the current state of the table does not contain rows with act3 or act4 .

When I try to change a column with a new set of values, it throws a Data Truncated for column action error.

Please suggest how to remove the required values.

Answer 1

Using ALTER TABLE to add enumeration values ​​in order is described in the MySQL documentation .

However, to remove enumeration values, the best option is to create a new column to modify.

 ALTER TABLE your_table ADD new_action_column ENUM('act1', 'act2') ... ; UPDATE your_table SET new_action_column = action; ALTER TABLE your_table DROP action; ALTER TABLE your_table CHANGE new_action_column action ENUM('act1', 'act2') ... ; 

---

Edit

By the way. Using ENUM is not a good idea, you should use INT instead.

8 Reasons MySQL ENUM Data Type Is Evil

I suggest you use matching, for example

 +------+-----+ | ENUM | INT | +======+=====+ | act1 | 0 | +------+-----+ | act2 | 1 | +------+-----+ 

Answer 2

Run the query first.

 UPDATE table_name SET action = '' WHERE action IN ( 'act3', 'act4' ); 

then run this query.

 ALTER TABLE table_name CHANGE action action ENUM( 'act1', 'act2' ); 

no need to drop the table or leave the field. but you must delete or update all data that has values ​​that you want to delete.

Answer 3

The other two answers have already examined the question in more detail, but here is a simple problem why you cannot just do ALTER TABLE. If you have ENUM ("BAR", "FOO", "REMOVEME"), and this gives an error saying something along the Data lines truncated by something, you may already have a record set to the Enum member itself, which you want to delete. So you first need to do something like

UPDATE yourtable SET enumrow = 'FOO' WHERE yourenumrow = 'REMOVEME';

Thus, all records with REMOVEME will now be FOO, and the table can be modified using

ALTER TABLE yourtable CHANGE yourenumrow yourenumrow ENUM ('FOO', 'BAR') DEFAULT NULL;