How do I update the members of a MySQL SET Type?
database, database-design, mysql
Solution
You want to add 'e' to the allowable values in that set field, or it's already there and you want to add 'e' to the current value of the set field in that table?
If it's not already an allowed value, then you'll have to do an ALTER TABLE and redefine the field:
ALTER TABLE set_test CHANGE myset myset SET('a','b','c','d','e');
(yes, 'myset' is put in there twice, it's a "currentname newname" sort of thing.)
Otherwise just do an UPDATE TABLE and concat the field with 'e' as the previous answer says.
Problem
I have a table of values, with one of the columns being of type SET. If it currently has the members ('a', 'b', 'c', 'd'), how do I add 'e' to the possible values? I realize that using a SET type is a little strange, and I'm unclear why one would use it instead of a foreign key to another table with the values of the set, but I didn't design the database in question, and can't change it that much. Thanks for your help! UPDATE: I want to update the SET type for all rows, not just one, if that helps.