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?

-------------Problems Reply------------

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

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

Category:sql Views:0 Time:2010-08-23
Tags: mysql sql types

Related post

  • Comma separated cells to rows, but preserve data in surrounding columns 2011-01-17

    Excel Macro - Comma Separated Cells to Rows Preserve/Aggregate Column My problem is almost exactly the same as the above link except I have data surrounding the columns I want to break out like the following: <- A (Category) -> <- B (Items)

  • jQueryUI Autocomplete: Returning data from multiple columns in a MySQL database and joining them 2011-11-26

    I am fairly new with jQuery, jQueryUI and its widgets. I've solved my problems with the dialog widget. One last thing before I can move on is the Autocomplete widget. To show you where I'm currently at, here's what I have so far. Javascript $(documen

  • Using Mysqli bind_param with date and time columns? 2009-04-30

    How do you insert data into a MySQL date or time column using PHP mysqli and bind_param? --------------Solutions------------- Like any other string $stmt = $mysqli->prepare('insert into foo (dt) values (?)'); $dt = '2009-04-30 10:09:00'; $stmt-

  • Any way for Doctrine HYDRATE_ARRAY to preserve data type? 2011-02-02

    I'm retrieving some data using Doctrine 1.2 ORM from a database on a symfony 1.4 project. Currently using a simple query and returning with: Doctrine::HYDRATE_ARRAY Is there any way to preserve data types using this method? All data types are cast to

  • Saving data to a column without deleting others in a Django script 2011-10-21

    I have a model in the form of: class Data(models.Model): some_id = models.IntegerField(primary_key=True) data1 = models.IntegerField() data2 = models.IntegerField() data3 = models.CharField(max_length=150) I'm reading in data from various files, and

  • Separate delimited Excel data into 2 columns 2012-01-05

    I have some email addresses in column A in Excel. There are many rows, however the names are attached in the same cell with each email address, so it looks like this: johnsmith:[email protected] adamsmith:[email protected] CoryAdam:[email protected]

  • data from on column to rows not finding pattern 2012-06-17

    I am trying to move data from one column simlar data set changes every 13 rows, I would like to move it to rows with 13 columums. I copy and transpose but that seems like alot to do 450 times. I have tried set up the first 3 or 4 rows then doing a pu

  • redim preserve data sorting order 2012-09-20

    hello, I have this function Public Function arrName(data As Variant, txtstr As String) As Variant ReDim Preserve data(0 To UBound(data) ) data(0) = txtstr arrName = data End Function suppose data has 5 strings in it. txtstr is a text box value. I wan

  • Separating data into individual columns 2012-11-17

    Converted a 97 page pdf report into Excel 2010. Was unable to separate the city, state, zip information into individual columns because it is all in one cell per record. What method would I use to separate this data involving 4000+ records on 97 indi

  • Propagating Dates Down A Column 2013-07-05

    I am looking for a way to propagate dates down a column. Normally if A1 contains a date, I would put =A1+1 in cell A2 and copy down the column. In this case I need the formula to preserve a space for 29 February if it is not needed. For example if Z1

  • Should I use NULL or an empty string to represent no data in table column? 2008-10-03

    Null or empty string -- is one better than the other to represent no data in a table column? (I specifically use MySQL, but I'm thinking this is system-independent.) Are there major advantages/disadvantages to using one over the other, or is it simpl

  • PHP MYSQL - Many data in one column 2008-11-15

    I need to store of 100-200 data in mysql, the data which would be separated by pipes.. any idea how to store it on mysql? should I use a single column or should I make many multiple columns? I don't know exactly how many data users will input. I made

  • SQL query to make all data in a column UPPER CASE? 2009-05-13

    I need a SQL query to make all data in a column UPPER CASE? Any ideas? --------------Solutions------------- Permanent: UPDATE MyTable SET MyColumn = UPPER(MyColumn) Temporary: SELECT UPPER(MyColumn) AS MyColumn FROM MyTable Additionally, if it was im

  • Preserve SQL Indexes While Altering Column Datatype 2009-08-11

    I have a smalldatetime column that I need to alter to be a datetime column. This is something that will be part of an install process, so it cannot be a manual procedure. Unfortunately, the column has a few indexes and a not null constraint on it. Th

  • using select query convert columns data as particular column 2009-09-10

    using select query convert columns data as particular column only using select query --------------Solutions------------- I think you are looking for pivot statement The PIVOT operator turns the values of a specified column into column names, effecti

  • Preserve data in .net mvc 2009-09-15

    I am implementing a search module with result page support paging. The example provided by NerdDinner passes pagenumber as a parameter for the Index action, and the action uses the pagenumber to perform a query each time the user hit a different page

  • Select records between two dates in two columns 2009-09-25

    How do I Select records between two dates in two columns? Select * From MyTable Where 2009-09-25 is between ColumnDateFrom to ColumnDateTo I have a date (2009-09-25) and I like to select the records that is in the timeframe ColumnDateFrom to ColumnDa

  • how to display data in two column with php 2009-10-31

    How can I display data in two columns? <?php $query = "SELECT * FROM `release` WHERE artist_id = '$rcode' AND label_id = '$id'"; $result = mysql_query($query); $tr_no = mysql_num_rows($result); while ($info = mysql_fetch_array($result)) { ?>

  • What is this error? "Database query failed: Data truncated for column 'column_name' at row 1 2009-11-09

    I'm building a PHP/MySQL application and I'm running into a problem with my create and update query. I have 5 columns that are set to type FLOAT that are also set as NULL columns. I don't plan to fill them in until much later in the workflow. However

Copyright (C), All Rights Reserved.

processed in 0.192 (s). 11 q(s)