510

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?

George Stocker
  • 57,289
  • 29
  • 176
  • 237
raji
  • 5,265
  • 2
  • 13
  • 4

9 Answers9

795
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.

Cynical
  • 9,328
  • 1
  • 15
  • 30
99

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;
Anthony
  • 36,459
  • 25
  • 97
  • 163
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • You can use Change or Modify column to do the same. Check the link which will give you the idea of updating a column using ALTER TABLE – Saharsh Shah Dec 20 '12 at 09:19
40

To delete a single column:

ALTER TABLE `table1` DROP `column1`;

To delete multiple columns:

ALTER TABLE `table1`
DROP `column1`,
DROP `column2`,
DROP `column3`;
Pikamander2
  • 7,332
  • 3
  • 48
  • 69
echo_Me
  • 37,078
  • 5
  • 58
  • 78
16

You can use

alter table <tblname> drop column <colname>
WAF
  • 1,003
  • 3
  • 15
  • 31
Kapil gopinath
  • 1,053
  • 1
  • 8
  • 18
15
ALTER TABLE `tablename` DROP `columnname`;

Or,

ALTER TABLE `tablename` DROP COLUMN `columnname`;
Sam
  • 7,252
  • 16
  • 46
  • 65
Avinash Nair
  • 1,984
  • 2
  • 13
  • 17
13

If you are running MySQL 5.6 onwards, you can make this operation online, allowing other sessions to read and write to your table while the operation is been performed:

ALTER TABLE tbl_Country DROP COLUMN IsDeleted, ALGORITHM=INPLACE, LOCK=NONE;
A. Colonna
  • 852
  • 7
  • 10
10

Use ALTER:

ALTER TABLE `tbl_Country` DROP COLUMN `column_name`;
Darren Shewry
  • 10,179
  • 4
  • 50
  • 46
Lo Juego
  • 1,305
  • 11
  • 12
8
ALTER TABLE tbl_Country DROP columnName;
Sam
  • 7,252
  • 16
  • 46
  • 65
Sterling Archer
  • 22,070
  • 18
  • 81
  • 118
3

It is worth mentioning that MySQL 8.0.23 and above supports Invisible Columns

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

INSERT INTO tbl_Country VALUES (1, 1), (2,0);

ALTER TABLE tbl_Country ALTER COLUMN IsDeleted SET INVISIBLE;

SELECT * FROM tbl_Country;
CountryId
1
2

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

db<>fiddle demo

It may be useful in scenarios when there is need to "hide" a column for a time being before it could be safely dropped(like reworking corresponding application/reports etc.).

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275