CODEWITHSUNDEEP
sqlmysqltypes
DLH
DLH

Reputation: 2821

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

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?

Upvotes: 7

Views: 2965

Answers (3)

AmbujMondal
AmbujMondal

Reputation: 11

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

Upvotes: 1

stark
stark

Reputation: 839

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

Upvotes: 9

SorcyCat
SorcyCat

Reputation: 1216

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

Upvotes: 1

Related Questions