How to delete a column from a table in MySQL

mysql ddl alter-table
raji picture raji · Dec 20, 2012 · Viewed 751.9k times · Source

Given the table created using: 

CREATE TABLE tbl_Country
(
  CountryId INT NOT NULL AUTO_INCREMENT,
  IsDeleted bit,
  PRIMARY KEY (CountryId) 
)

How can I delete the column IsDeleted?

Answer

Cynical picture Cynical · Dec 20, 2012 
ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

Here's a working example.

Note that the COLUMN keyword is optional, as MySQL will accept just DROP IsDeleted. Also, to drop multiple columns, you have to separate them by commas and include the DROP for each one.

ALTER TABLE tbl_Country
  DROP COLUMN IsDeleted,
  DROP COLUMN CountryName;

This allows you to DROP, ADD and ALTER multiple columns on the same table in the one statement. From the MySQL reference manual:

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause per ALTER TABLE statement.

Related questions

MySQL: ALTER TABLE if column not exists

I have this code: ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1 And I want to alter this table only if this column doesn't exist. I'm trying a lot of different ways, but nothing works: ALTER TABLE `…

Read More
ALTER TABLE without locking the table?

When doing an ALTER TABLE statement in MySQL, the whole table is read-locked (allowing concurrent reads, but prohibiting concurrent writes) for the duration of the statement. If it's a big table, INSERT or UPDATE statements could be blocked for a …

Read More
Adding multiple columns AFTER a specific column in MySQL

I need to add multiple columns to a table but position the columns after a column called lastname. I have tried this: ALTER TABLE `users` ADD COLUMN ( `count` smallint(6) NOT NULL, `log` varchar(12) NOT NULL, `status` int(10) unsigned NOT NULL ) …

Read More