How do I update the members of a MySQL SET Type?

Question

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.

Answer 1

To add an element to an existing SET use the CONCAT() function to add the new element to your comma separated list. To work with decimal values, we can use the bitwise OR operator |.

  UPDATE set_test SET myset = CONCAT(myset,",Travel")
      WHERE rowid = 3;

or

  UPDATE set_test SET myset = myset | 1 WHERE rowid = 3;

You can also use the CONCAT_WS() function, which handles list separators for us:

  UPDATE set_test SET myset = CONCAT_WS(',',myset,'Dancing')
      WHERE rowid = 6;

Answer 2

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.