MySQL: SQL to drop a column from a tableMySQL: 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.

Related posts:

Share or Bookmark

Share or Bookmark this page using the following services. You will need to have an account with the selected service in order to post links or bookmark this page.

Subscribe or Follow

Subscribe via RSS or email, or follow me on Facebook or Twitter below. The RSS icon takes you through to Feedburner where you can select the service or application to use.

Comments

blog comments powered by Disqus