Hi

I've got an enum column in a table with values as follows:

Quote:

enum('*','* *','* * *','* * * *','* * * * *')

I need to add some additional values to it, could you confirm if the following would be the correct statement to use:

ALTER TABLE tablename CHANGE columnname enum('*','* *','* * *','* * * *','* * * * *','new1','new2','etc,etc');

I think the above is correct, but don't want to mess up.

Thanks.

sorry , forgot to add I also need to be able to chose more than 1 of the options listed at the same time, so could I put SET in place of ENUM in the above example?

Thanks

You're looking for "modify" rather than "change", but beyond that your syntax looks fine.   I strongly suggest that you (1) do a mysqldump before you modify your tables so that you can get back in the event of a problem and (2) test your commands first on another server / table not the live data.   I ran a test before I answered you and here are my results - they confirm the syntax

Code:

mysql> create table demo (    ->        id int primary key auto_increment,    ->        person text,    ->        role enum("customer","supplier"),    ->        phone text) ; Query OK, 0 rows affected (0.75 sec) mysql> insert into demo (person, role, phone) values    ->       ("Chris","customer","01622 222222"),    ->       ("David","customer","01717 717717"),    ->       ("Joan","customer","0117 9090 8080"); Query OK, 3 rows affected (0.15 sec) Records: 0  Duplicates: 3  Warnings: 0 mysql> select * from demo ; +----+--------+----------+----------------+ | id | person | role     | phone          | +----+--------+----------+----------------+ |  1 | Chris  | customer | 01622 222222   | |  2 | David  | customer | 01717 717717   | |  3 | Joan   | customer | 0117 9090 8080 | +----+--------+----------+----------------+ 3 rows in set (0.00 sec) mysql> mysql> alter table demo modify role enum("customer","supplier","prospect"); Query OK, 3 rows affected (0.10 sec) Records: 3  Duplicates: 0  Warnings: 0 mysql> alter table demo modify role set("customer","supplier","prospect","friend"); Query OK, 3 rows affected (0.01 sec) Records: 3  Duplicates: 0  Warnings: 0 mysql> describe demo; +--------+------------------------------------------------+------+-----+---------+----------------+ | Field  | Type                                           | Null | Key | Default | Extra          | +--------+------------------------------------------------+------+-----+---------+----------------+ | id     | int(11)                                        |      | PRI | NULL    | auto_increment | | person | text                                           | YES  |     | NULL    |                | | role   | set('customer','supplier','prospect','friend') | YES  |     | NULL    |                | | phone  | text                                           | YES  |     | NULL    |                | +--------+------------------------------------------------+------+-----+---------+----------------+ 4 rows in set (0.09 sec) mysql> insert into demo (person, role) values ("John","customer,friend"); Query OK, 1 row affected (0.62 sec) mysql> select * from demo; +----+--------+-----------------+----------------+ | id | person | role            | phone          | +----+--------+-----------------+----------------+ |  1 | Chris  | customer        | 01622 222222   | |  2 | David  | customer        | 01717 717717   | |  3 | Joan   | customer        | 0117 9090 8080 | |  4 | John   | customer,friend | NULL           | +----+--------+-----------------+----------------+ 4 rows in set (0.00 sec) mysql>

Cheers, worked like a dream.

---