How do I preserve data when changing column datatypes in MySQL?

Question

For example, suppose I have a TINYINT column that I want to change into an ENUM. Is it possible to write a MySQL query that changes the datatype of the column while mapping the existing data so that (for example) 0 becomes No, 1 becomes Yes, and 2 becomes Maybe?

Answer 1

I'm not sure if this is possible in MySQL, but an alternative solution would be to:

1. Add the new column with the new type with a new name
2. Set the new column values (given the old column values)
3. Drop the old column
4. Rename the new column to have the same name as the old column

Answer 2

create the new column next to the old column, use an update to move the data over, then delete the original column.

Answer 3

This needs a sequence of 4 steps- add new column, set data in new column, drop old column, rename new column

Use [Databasename]
Go
ALTER table [tableName]
Add [newColumnname] datatype
GO
Update [tableName] SET [newColumnname] = [oldColumnname]
GO
ALTER table [tableName] Drop Column [oldColumnname]
GO
EXEC sp_RENAME 'tableName.newColumnname' , 'oldColumnname', 'COLUMN'
GO