How to modify a column if it happens to be a MySQL type?

Question

I have a table called sidebar_items and it has a Type column, which is of type enum('image', 'html'). I would like to change this column to be of type enum('image', 'html', 'structure'). I have tried this:

alter table sidebar_items modify Type Type enum('image', 'html', 'structure');

It gives me the error of

alter table sidebar_items modify Type Type enum('image', 'html', 'structure')   Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Type enum('image', 'html', 'structure')' at line 1

I have tried with `Type` as well. Can I make this query work?

Alternatively, I can resolve the problem by:

• creating a temp table with the same structure as sidebar_items
• migrating the records from sidebar_items to the temp table
• dropping sidebar_items
• recreating sidebar_items with the new type for Type
• migrating the records from the temp table to sidebar_items
• dropping the temp table

However, I am interested to know whether there are any simpler solutions, possibly with a single alter table command.

Answer 1

The correct syntax is:

ALTER TABLE sidebar_items MODIFY `Type` ENUM('image', 'html', 'structure');

with the ALTER TABLE ... MODIFY command, you do not need to specify the column name twice, but you'd need it for ALTER TABLE ... CHANGE.

You can rename a column using a CHANGE old_col_name new_col_name column_definition clause. To do so, specify the old and new column names and the definition that the column currently has. For example, to rename an INTEGER column from a to b, you can do this:

ALTER TABLE t1 CHANGE a b INTEGER;

To change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same. For example:

ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

You can also use MODIFY to change a column's type without renaming it:

ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

MODIFY is an extension to ALTER TABLE for Oracle compatibility.