How to Delete a Column From a Table in MySQL

1. Create MySQL Database
2. Delete a Single Column From a MySQL Table
3. Delete Multiple Columns From a MySQL Table
4. Conclusion

We may sometimes need to remove single or numerous columns from a database table. The ALTER TABLE DROP COLUMN command statement in MySQL can remove a column from a table.

The following is the usual syntax for accomplishing this:

ALTER TABLE table_name DROP COLUMN column_name;

The following is the explanation for the above syntax:

• The name of the table where the column will be removed.
• The DROP COLUMN phrase defines the column to be deleted and its name.

This article walks you through deleting a column from a table in MySQL.

Create MySQL Database

Step 1: Create MySQL Database and Table

The first step is to create a database and a table to show the method above.

CREATE DATABASE CountryDB;
USE CountryDB;

CREATE TABLE tbl_Country
 (
     CountryId INT NOT NULL AUTO_INCREMENT,
     CountryCode varchar(50),
     CountryName varchar(50),
     IsActive bit,
     IsDeleted bit,
     PRIMARY KEY (CountryId) 
 );

Step 2: Insert Data Into MySQL Table

To insert data into the already created tbl_Country table, paste and run the code snippet provided.

INSERT INTO tbl_Country (CountryCode,CountryName,IsActive,IsDeleted)
VALUES ("A","Country A",1, 1), 
		("B","Country B",1,0),
		("C","Country C",1, 1), 
		("D","Country D",1,1);

To see the output, run a SELECT command.

SELECT * FROM tbl_Country;

Output:

| CountryId | CountryCode | CountryName | IsActive | IsDeleted |
| :-------- | :---------- | :---------- | :------- | :-------- |
| 1         | A           | Country A   | 1        | 1         |
| 2         | B           | Country B   | 1        | 0         |
| 3         | C           | Country C   | 1        | 1         |
| 4         | D           | Country D   | 1        | 1         |

Step 3: Delete a Column From a Table in MySQL

You can use the alter table MySQL command to drop a column from the table below.

The general syntax of dropping table columns is shown below.

alter table <tblname> drop column <colname>

In this context, to delete the IsDeleted column from the tbl_Country table, paste the below code.

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

Delete a Single Column From a MySQL Table

The syntax for deleting a single column from a MySQL table is below.

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

To see the output of the above command, run a SELECT command.

SELECT * FROM tbl_Country; 

| CountryId | CountryCode | CountryName | IsActive |
| :-------- | :---------- | :---------- | :------- |
| 1         | A           | Country A   | 1        |
| 2         | B           | Country B   | 1        |
| 3         | C           | Country C   | 1        |
| 4         | D           | Country D   | 1        |

Delete Multiple Columns From a MySQL Table

MySQL also provides for the deletion of multiple columns. Suppose you want to delete multiple columns simultaneously, use the below query, with the column names separated by commas.

ALTER TABLE tbl_Country
  DROP COLUMN IsActive,
  DROP COLUMN CountryName;

Use the code below to show the result:

SELECT * FROM tbl_Country; 

Output:

| CountryId | CountryCode |
| :-------- | :---------- |
| 1         | A           |
| 2         | B           |
| 3         | C           |
| 4         | D           |

Conclusion

Columns in databases contain cells used to store particular rows’ values in a table. The above discussion has covered the use of the ALTER TABLE command in MySQL to show you how to delete columns from a table given the column names.

Since MySQL is a relational database, it is imperative to note that column deletion affects the relationship constraints within the database system and its performance during the query execution.