How to delete a column from a table in MySQL
Solution 1:
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
, andCHANGE
clauses in a singleALTER TABLE
statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause perALTER TABLE
statement.
Solution 2:
Use ALTER TABLE
with DROP COLUMN
to drop a column from a table, and CHANGE
or MODIFY
to change a column.
ALTER TABLE tbl_Country DROP COLUMN IsDeleted;
ALTER TABLE tbl_Country MODIFY IsDeleted tinyint(1) NOT NULL;
ALTER TABLE tbl_Country CHANGE IsDeleted IsDeleted tinyint(1) NOT NULL;
Solution 3:
To delete a single column:
ALTER TABLE `table1` DROP `column1`;
To delete multiple columns:
ALTER TABLE `table1`
DROP `column1`,
DROP `column2`,
DROP `column3`;
Solution 4:
You can use
alter table <tblname> drop column <colname>