Wednesday, May 6, 2009

MySql: Drop a column from an existing MySQL table


Share at Facebook

Lets talk about dropping an unnecessary column from existing mysql table. Using ALTER key and DROP key, you can easily delete any column from your old table. But remember, it will delete the column with data. So always be careful to do such kind of work.

Lets say we have a mysql table info as below.
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, from the above table, We just want to remove the dept column. What will be the mysql query to do that? It will be very simple. Just look at below code. Using the DROP key after ALTER its easily removed.
mysql> ALTER TABLE info DROP COLUMN dept;
So the table will look like as below.
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+

See, the dept column is just gone.




No comments: