CODEWITHSUNDEEP
mysqldatabase-designenums
stefmikhail
stefmikhail

Reputation: 7125

Changing the Value of a MySQL ENUM Value, Throughout a Table

I'm wondering if it is possible to change an ENUM value throughout a table, so that in all the rows where said ENUM value is represented, the change is made as well.

Upvotes: 18

Views: 16475

Answers (1)

Johan
Johan

Reputation: 76557

If you want to change the value of an enum:

Suppose your old enum was:

ENUM('English', 'Spanish', 'Frenchdghgshd', 'Chinese', 'German', 'Japanese')

To change that use:

--  Add a new enum value
ALTER TABLE `tablename` CHANGE `fieldname` `fieldname` ENUM   
('English', 'Spanish', 'Frenchdghgshd', 'Chinese', 'German', 'Japanese', 'French');
-- Update the table to change all the values around.
UPDATE tablename SET fieldname = 'French' WHERE fieldname = 'Frenchdghgshd';
-- Remove the wrong enum from the definition
ALTER TABLE `tablename` CHANGE `fieldname` `fieldname` ENUM   
('English', 'Spanish', 'Chinese', 'German', 'Japanese', 'French');

MySQL will probably go through all the rows in your table trying to update stuff, I've heard stories of a planned optimization around that, but I'm not sure if that actually happened.

Upvotes: 31

Related Questions