CODEWITHSUNDEEP
mysqlenumsalter
mickeymoon
mickeymoon

Reputation: 4977

Removing enum values from mysql column

I have a table with a enum column called action. The permitted values currently are: act1,act2,act3,act4. I want act3 and act4 to be removed and my table's current state does not contain any rows with act3 or act4.

When I'm trying to modify the column with the new set of values it's throwing an error Data Truncated for column action.

Please suggest how do I remove the required values.

Upvotes: 11

Views: 10771

Answers (3)

Zain
Zain

Reputation: 105

The other two answers already covered the question in larger detail, but here is a simple issue why you may not be able to simply do ALTER TABLE. If you have an ENUM('BAR','FOO','REMOVEME') and it gives an Error saying something along the lines of Data truncated somethingsomething, you might already have an entry set to the very Enum member you want to remove. So you'd first need to do something like

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

This way, all entries that had REMOVEME will now be FOO and the table can be altered using

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

Upvotes: 3

Florian Parain
Florian Parain

Reputation: 1099

Using ALTER TABLE for adding enum values is ok and described in the MySQL documentation.

However, for removing enum values the better option is to create a new column to do the change.

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 the best idea, you should use INT instead.

8 Reasons Why MySQL's ENUM Data Type Is Evil

I suggest you to use a mapping like

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

Upvotes: 11

Bhoopesh Pathak
Bhoopesh Pathak

Reputation: 179

First run a query.

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

after this run this query.

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

there is no need to drop your table or drop your field. but you are required to delete or update all data having the values, which you want to remove.

Upvotes: 4

Related Questions