MySQL Forums
Forum List  »  Newbie

Re: Need to change datatype for an enumerated field
Posted by: Rick James
Date: June 09, 2009 11:28PM

Three step process...
mysql> create table enumr (e enum('a','b','c') not null);
mysql> insert into enumr (e) values ('a'),('b'),('c');
mysql> select * from enumr;
+---+
| e |
+---+
| a |
| b |
| c |
+---+
# Trying to do in one step:
mysql> alter table enumr modify column e  enum('a','xx','c') not null;
ERROR 1265 (01000): Data truncated for column 'e' at row 2
# No harm done (I guess):
mysql> select * from enumr;
+---+
| e |
+---+
| a |
| b |
| c |
+---+
# (STEP 1) OK, add the new value:
mysql> alter table enumr modify column e  enum('a','b','c','xx') not null;
Query OK, 3 rows affected (0.48 sec)
Records: 3  Duplicates: 0  Warnings: 0
# No obvious change:
mysql> select * from enumr;
+---+
| e |
+---+
| a |
| b |
| c |
+---+
# (STEP 2) Now update the rows with the value I want to change:
mysql> update enumr set e='xx' where e='b';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0
# Looks good:
mysql> select * from enumr;
+----+
| e  |
+----+
| a  |
| xx |
| c  |
+----+
# (STEP 3) Now, get rid of the old 'b':
mysql> alter table enumr modify column e  enum('a','c','xx') not null;
Query OK, 3 rows affected (0.22 sec)
Records: 3  Duplicates: 0  Warnings: 0
# and double check:
mysql> select * from enumr;
+----+
| e  |
+----+
| a  |
| xx |
| c  |
+----+

Options: ReplyQuote


Subject
Written By
Posted
Re: Need to change datatype for an enumerated field
June 09, 2009 11:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.