Let's say I have a field that is enum('option1','option2') and in that table there are various records that contain either option1 or option2.
I want to change it from enum('option1','option2') to enum('option1','option3') and, at the same time change all records that are option2 to option3....how do I do this without running into the following catch-22:
If I first change the field type to enum('option1','option3') then the query won't be valid because some records contain option2
If I first try to change all records with option2 to option3, then the query won't work because the field type is still enum('option1','option2') and option3 isn't a valid value.
How do I do it? The only thing I can think of is to create a temporary table, write all the data to it, truncate the current table, alter the fields appropriately, read all the data from the temporary table into the newly altered table, and then delete the temporary table.....seems like an awful lot of work, but is this the proper approach? Or is there a way for dealing with this catch-22?