www.webdeveloper.com
Results 1 to 2 of 2

Thread: Altering SET or ENUM in tables containing data

  1. #1
    Join Date
    Dec 2005
    Posts
    2,984

    Altering SET or ENUM in tables containing data

    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?
    I've switched careers...
    I'm NO LONGER a scientist,
    but now a web developer...
    awesome.

  2. #2
    Join Date
    Jul 2005
    Location
    USA
    Posts
    912
    add all 3 to the enum, change your data, then remove the extra one from the column def

    enum('option1','option2','option3')

    UPDATE _table_ SET ....

    enum('option1','option3')
    Bitter web veteran

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles