JIYIK CN >

Sometimes we may need to delete single or multiple columns from a database table. ALTER TABLE DROP COLUMNThe command statement in MySQL can delete a column from a table.

Here is the common syntax to accomplish this:

ALTER TABLE table_name DROP COLUMN column_name;

Here is an explanation of the above syntax:

• The name of the table from which the column will be dropped.
• DROP COLUMNThe phrase defines the columns to be dropped and their names.

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

Create MySQL Database

Step 1: Create a MySQL database and table

The first step is to create a database and a table to demonstrate the above methods.

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 created tbl_Countrytable, paste and run the provided code snippet.

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 view the output, run SELECTthe 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: Remove columns from the table in MySQL

You can alter tabledelete a column from the following table using MySQL command.

The general syntax for dropping a table column is as follows.

alter table <tblname> drop column <colname>

In this case, to tbl_Countrydelete IsDeletedthe column from the table, paste the following code.

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

Deleting a single column from a MySQL table

The syntax to delete a single column from a MySQL table is as follows.

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

To view the output of the above command, run SELECTthe 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        |

Deleting multiple columns from a MySQL table

MySQL also provides multiple column deletion. Suppose you want to delete multiple columns at the same time, use the following query, with the column names separated by commas.

ALTER TABLE tbl_Country
  DROP COLUMN IsActive,
  DROP COLUMN CountryName;

Use the following code to display the results:

SELECT * FROM tbl_Country; 

Output:

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

in conclusion

Columns in a database contain cells that are used to store values ​​for a particular row in a table. The above discussion covers the use of Delete ALTER TABLEcommand in MySQL to show you how to delete a column from a table given a column name.

Because MySQL is a relational database, it is important to be aware that column deletion affects relational constraints within the database system and its performance during query execution.