Menu
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.
351
You can add a new value to a column of data type enum using ALTER MODIFY command. If you want the existing value of enum, then you need to manually write the existing enum value at the time of adding a new value to column of data type enum.
MySQL ENUM data type contains the following advantages: Compact data storage where the column may have a limited set of specified possible values. Here, the string values automatically used as a numeric index. It allows readable queries and output because the numbers can be translated again to the corresponding string.
If an ENUM column is declared to permit NULL , the NULL value is a valid value for the column, and the default value is NULL . If an ENUM column is declared NOT NULL , its default value is the first element of the list of permitted values.
If you want to determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE enum_col and parse the ENUM definition in the Type column of the output.
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.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
