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.

38 Responses to “Delete a column from an existing MySQL table”

  1. November 01, 2008 at 8:40 am, Thennarasu said:

    Ya it works…

    Reply

  2. December 07, 2008 at 8:18 am, kiran said:

    helpful in coding.thank u.

    Reply

  3. January 03, 2009 at 4:43 am, Dan said:

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

    Reply

  4. January 07, 2009 at 5:36 am, Mercy said:

    Thank u. It is useful

    Reply

  5. January 20, 2009 at 4:15 pm, mysql said:

    use it without ( ‘ )…

    Reply

    • February 13, 2009 at 6:34 pm, steve said:

      yep:

      ALTER TABLE table_things DROP col_stuff

      Reply

      • July 30, 2010 at 7:05 am, Sss said:

        love u

        Reply

  6. February 24, 2009 at 7:42 am, Neelesh said:

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

    Reply

  7. April 16, 2009 at 4:36 am, jaswath said:

    thanks it solved problem

    Reply

  8. April 21, 2009 at 11:40 am, vol said:

    ALTER TABLE table_things DROP col_stuff;

    Reply

  9. June 15, 2009 at 9:48 pm, A said:

    ALTER TABLE room DROP COLUMN y;

    Reply

  10. December 11, 2009 at 1:30 pm, dennis said:

    ALTER TABLE `article` DROP column `homepage`;

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

    Reply

  11. March 12, 2010 at 11:59 pm, Anonymous said:

    Exactly what I needed. Thanks.

    Reply

    • June 03, 2010 at 3:43 am, vishnu said:

      yes…….Thanks for

      Reply

    • February 10, 2011 at 4:36 am, Abcdasdasdasd said:

      Exactly what I needed. Thanks.

      Reply

  12. June 24, 2010 at 10:39 am, DID said:

    It Dosn`t work in my machine

    Reply

    • July 07, 2010 at 6:30 am, Aishwarya said:

      Thanks for ur valuable information

      Reply

      • August 25, 2010 at 10:36 am, Rahuljain Iips said:

        Gud One

        Reply

    • October 01, 2010 at 5:04 am, Test said:

      its working

      Reply

  13. December 18, 2010 at 8:04 pm, Anonym said:

    Thanks so much!

    Reply

  14. February 25, 2011 at 3:07 pm, sam said:

    thanks

    Reply

  15. March 19, 2011 at 5:04 pm, Arun mohapatra said:

    Thans a lot dear….

    Reply

  16. April 01, 2011 at 10:05 am, Kili said:

    poda…………..

    Reply

  17. April 29, 2011 at 1:32 pm, Mail said:

    thanks works

    Reply

  18. June 30, 2011 at 6:12 pm, Pawankjajara said:

    Awesome Query, works fine !

    Reply

  19. October 09, 2011 at 12:13 am, Karthikeyan said:

    Thank you so much dude. Good Work.

    Reply

  20. March 13, 2012 at 6:01 am, prakash said:

    pretty nice

    Reply

  21. May 13, 2012 at 4:29 pm, jyothi said:

    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?

    Reply

  22. May 13, 2012 at 4:37 pm, jyothi said:

    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

  23. June 05, 2012 at 10:07 am, Ismed said:

    Nice tutorial. Thnks

    Reply

  24. June 11, 2012 at 10:03 am, sri said:

    see dis for sql 2005

    alter table numbers drop column num1

    alter table drop column

    Reply

  25. June 29, 2012 at 5:45 am, yogesh said:

    thanks….. it was usefull

    Reply

  26. September 08, 2012 at 6:22 am, Sukhpal singh said:

    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

    Reply

    • November 02, 2012 at 2:53 am, SackofBeans said:

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

      Reply

  27. January 20, 2013 at 9:43 am, Ivan said:

    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

    Reply

  28. February 10, 2013 at 3:10 am, Kartik said:

    Thank you!! :D

    Reply

  29. June 06, 2013 at 7:40 am, sabin said:

    o I didn’t know this

    Reply

  30. February 17, 2014 at 1:04 am, viresh kumar said:

    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

    Reply

Leave a Reply