MySQL: SQL to drop a column from a table

Posted November 25th, 2009 in MySql

It's probably more common for people to edit MySQL tables using a more visual tool such as phpMyAdmin but it is possible to use a manually written SQL query instead to modify tables (this is what a tool like phpMyAdmin does in the background). This post shows how to drop a column from a MySQL table with a SQL query.

Drop a single column

The query to drop a single column is very simple. In the example table "test" there is a field called "foo" that is to be dropped. This is done like so:

ALTER TABLE test
DROP COLUMN foo;

Depending how large the table is this may take a few milliseconds to a number of minutes to run.

Drop multiple columns

Multiple columns can be dropped with a single query by simply comma separating a list of DROP COLUMN statments. To drop both the "foo" and "bar" columns from the "test" table do this:

ALTER TABLE test
DROP COLUMN foo,
DROP COLUMN bar;

As many additional columns can be added to the list as required. Again, this may only take a few milliseconds to many minutes depending on the size of the data in the table.