Geek Answers Handbook

How to save data when changing column data types in MySQL?

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

+4
source share
3 answers

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

  • Add a new column with a new type with a new name
  • Set new column values ​​(given the values ​​of the old column)
  • Discard the old column
  • Rename the new column with the same name as the old column.
+5
source

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

+1
source

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

0
source

More articles:


All Articles