53

I'm trying to drop multiple columns, but having some trouble. The syntax below works when I do ALTER TABLE and ADD with multiple values in the brackets () but it doesn't work with DROP COLUMN. Am I using the wrong syntax?

    $table3 = "
        ALTER TABLE $table3_name
        DROP COLUMN (
            user_firstname,
            user_lastname,
            user_address,
            user_address2,
            user_city,
            user_state,
            user_zip,
            user_phone
        );
    ";
Cory Nickerson
  • 883
  • 2
  • 11
  • 19
  • 1
    There is no a such syntax in the documentation, you must use something like `ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;` – MatRt Apr 04 '13 at 23:34
  • It seems like the answer was in this [question](http://stackoverflow.com/q/3894789). – HamZa Apr 04 '13 at 23:39
  • Possible duplicate of [How do I DROP multiple columns with a single ALTER TABLE statement?](http://stackoverflow.com/questions/6346120/how-do-i-drop-multiple-columns-with-a-single-alter-table-statement) – Veve Feb 02 '17 at 23:33

2 Answers2

102
ALTER TABLE `tablename`
DROP `column1`,
DROP `column2`,
DROP `column3`;
void
  • 1,876
  • 8
  • 24
  • 30
  • This is what I tried next and it worked. Just thought it was odd how the other syntax didn't work when it works for other queries. Thanks. – Cory Nickerson Apr 04 '13 at 23:34
  • @CoryNickerson Dropping is different from other functions such as select & replace for example – Daryl Gill Apr 04 '13 at 23:35
  • 2
    No problem, don't forget to have back-ups, though :) – void Apr 04 '13 at 23:35
  • A more concise way can be found in this answer: http://stackoverflow.com/questions/6346120/how-do-i-drop-multiple-columns-with-a-single-alter-table-statement – thdoan Oct 16 '14 at 03:06
  • @10basetom In the answer you linked the syntax for MySQL was the same as this one. The more concise form was non-MySQL. – cesoid May 12 '17 at 19:46
4

To drop multiple columns actual syntax is

alter table tablename drop column col1, drop column col2 , drop column col3 ....

So for every column you need to specify "drop column" in Mysql 5.0.45. This is same as that of above answer. Just adding one point here. Alter table wont free up the actual space on the disk. So run optimize table on after running optimize table.

MANISH ZOPE
  • 1,181
  • 1
  • 11
  • 28