How to: MySQL Delete Column

by on January 17, 2009 · 4 comments· LAST UPDATED October 17, 2009

in , ,

How do I delete a column from an existing MySQL table using UNIX / Windows / Linux mysql command line utility sql syntax?

You need to use the ALTER TABLE syntax to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, and much more with ALTER TABLE.

Step # 1: Login to mysql

Type the following command:
mysql -u user -p databasename

Step 2: Delete column

First see table description:
desc tableName
Use the following syntax at mysql>
ALTER TABLE tableName DROP columnName;
For example, see t1 table description, enter:

DESC t1;

Sample outputs:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int(11)     | YES  |     | NULL    |       |
| c2    | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Delete column c2, enter:

ALTER TABLE t1 DROP c2;
DESC t1

Sample outputs:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 4 comments… read them below or add one }

1 Azeem May 4, 2012 at 12:21 pm

I want to delete column data only not row for that i have write that query but it doesn’t work so any idea please share me

DELETE title FROM thinks_books WHERE title=’urdu’,
Thanks
Azeem Akram

Reply

2 lijubahulayan September 5, 2012 at 10:31 am

Dear Azeem, try to execute “UPDATE ” query instead of DELETE

UPDATE thinks_books SET title=” ” WHERE title=’Urudu’;

Reply

3 leela May 13, 2012 at 4:34 pm

I created a table in mysql containing 2 columns ,userid,movieid.I entered the values in movieid column using arraylist.

the table contains the following data

userid movieid

1 12234,5688,8976
2 567

now i want to delete 12234 in movieid column where userid=1
how i can write the query in mysql.can anyone help me.

Reply

4 mathews March 12, 2013 at 12:06 pm

how to show agin table

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , , , , , ,

Previous Faq:

Next Faq: