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;