Delete a column from an existing MySQL table

The SQL command in this recipe removes a column and the column’s data from an existing MySQL table.


To delete the column col_stuff from the table table_things, use the following SQL command:

ALTER TABLE 'table_things' DROP 'col_stuff'

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

  • Thennarasu

    Ya it works…

  • kiran

    helpful in coding.thank u.

  • http://www.NetthruOffice.com Dan

    try myNet.Office
    at: http://www.NetthruOffice.com

  • Mercy

    Thank u. It is useful

  • mysql

    use it without ( ‘ )…

    • steve

      yep:

      ALTER TABLE table_things DROP col_stuff

      • Sss

        love u

  • Neelesh

    If we want to check whether that column exit, if exist only then delete the column so that our script does not hamper.

  • jaswath

    thanks it solved problem

  • vol

    ALTER TABLE table_things DROP col_stuff;

  • A

    ALTER TABLE room DROP COLUMN y;

  • http://www.coscms.org/ dennis

    ALTER TABLE `article` DROP column `homepage`;

    Need to use key word column with my version of mysql. 5.1.41-1

  • Anonymous

    Exactly what I needed. Thanks.

    • vishnu

      yes…….Thanks for

    • Abcdasdasdasd

      Exactly what I needed. Thanks.

  • DID

    It Dosn`t work in my machine

    • Aishwarya

      Thanks for ur valuable information

      • Rahuljain Iips

        Gud One

    • Test

      its working

  • Anonym

    Thanks so much!

  • sam

    thanks

  • Arun mohapatra

    Thans a lot dear….

  • Kili

    poda…………..

  • Mail

    thanks works

  • Pawankjajara

    Awesome Query, works fine !

  • Karthikeyan

    Thank you so much dude. Good Work.

  • prakash

    pretty nice

  • jyothi

    i created a table in mysql,containing columns uesrid,movieid.In movieid column i insert the values using array
    as 1234,4567,7890.now i want to delete the 1234 in movieid column using mysql.can any one help me?

  • jyothi

    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.

  • Ismed

    Nice tutorial. Thnks

  • sri

    see dis for sql 2005

    alter table numbers drop column num1

    alter table drop column

  • yogesh

    thanks….. it was usefull

  • http://about.me/sukhpal3y Sukhpal singh

    when i use the command: alter table student drop column roll_no;
    it gives the following keyword… plz give the reason… ORA-00905: missing keyword

    • SackofBeans

      > You are using an oraacle database… this is for mysql, look for oracle specific syntax to delete column

  • Ivan

    I’ dont know which version of mySql are you using but this doesn work in all version. You forgot to add drop column .
    Example:
    ALTER TABLE ‘table_things’ DROP COLUMN ‘col_stuff’

    This works 100% in all version

  • Kartik

    Thank you!! :D

  • sabin

    o I didn’t know this

  • viresh kumar

    how to drop the column “emp_id” from table worker ?

    SQL> select * from worker ;

    EMP_NO EMP_NAME EMP_ADD EMP_CITY EMP_D EMP_SALARY
    ——— ———- ———- ——– —– ———- ———-
    1 viresh rohini delhi cse 20,000
    2 kanika snp hry cse 20,000
    3 vishal rohini music cse 15,000
    4 priya snp hry cse 12,000
    5 vineet narela delhi dance 20,000