Wednesday, May 6, 2009

MySql: Modify a column from an existing MySQL table


Share at Facebook

I have discussed about add/drop of column at my last two posts. Now its time to learn about updating/modifying an existing column from your existing table. Lets consider the info table once again. It has below structure.

mysql> create table info( name varchar(255), id int(11), dept varchar(5));

+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| dept | varchar(5) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+


Now I want to update the dept column from varchar to int data-type. How to do that? Once again the ALTER key, and the MODIFY key after that. It will replace the data type of the dept column. Here is the mysql query for updating a column data type.

ALTER TABLE info MODIFY column dept int(11);

Here, you just need to pass the column name, and the new data type. Once again, it will make you to loss the data for this column as you are changing the data-type. So the new table will be as below. Just the dept column's data type is changed.
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| dept | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+