How to delete column in table

Introduction

• In SQL, sometimes it is required to delete a column of a table.
• The use of ALTER TABLE command with DROP COLUMN clause will serve the purpose to delete/remove a column from a table.
• It is possible to remove single as well as multiple columns from a table.

1. Delete a single column from a table

To delete a single column of the table, use the syntax given below:

ALTER TABLE TableName DROP COLUMN ColumnName;

Here,

• TableName is the name of table whose column is to be deleted.
• ColumnName after the DROP COLUMN clause is the name of the column which is to be deleted.

          Example:

Consider a database with name “employee_db” with a table ‘employee’ created into it. In this topic, we will consider this table and database for all the subsequent examples:

mysql> USE employee_db;

Database changed
mysql> DESC employee;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Emp_ID      | int(11)     | NO   | PRI | NULL    |       |
| Emp_Name    | varchar(20) | YES  |     | NULL    |       |
| Emp_Salary  | int(11)     | YES  |     | NULL    |       |
| Emp_Dept    | varchar(20) | YES  |     | NULL    |       |
| Emp_City    | varchar(20) | YES  |     | NULL    |       |
| Emp_PhoneNo | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.07 sec)


mysql> SELECT *FROM employee;
+--------+----------+------------+------------+----------+-------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept   | Emp_City | Emp_PhoneNo |
+--------+----------+------------+------------+----------+-------------+
|    101 | Ram      |      52000 | R&D        | Pune     | 8798654676  |
|    102 | Shyam    |      38000 | Finance    | Delhi    | 9898765687  |
|    103 | Anmol    |      61000 | Accounting | Mumbai   | 9087864532  |
|    104 | Abhishek |      69000 | Purchasing | Shimla   | 7678987534  |
|    105 | Rohit    |      53000 | HRM        | Ambala   | 8897865643  |
+--------+----------+------------+------------+----------+-------------+
5 rows in set (0.00 sec)

Here, we have selected the already created database with ‘USE employee_db’ command. “DESC employee” command describes the structure of ‘employee’ table. Then we have used the SELECT command to display the employee table created into employee_db.

Now, we will write a query to delete a column containing employee city in ‘employee’ table.

mysql> ALTER TABLE employee DROP COLUMN Emp_City;
Query OK, 5 rows affected (0.30 sec)
Records: 5  Duplicates: 0  Warnings: 0

ALTER TABLE command is used on employee table with DROP COLUMN clause on Emp_City.

mysql> DESC employee;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Emp_ID      | int(11)     | NO   | PRI | NULL    |       |
| Emp_Name    | varchar(20) | YES  |     | NULL    |       |
| Emp_Salary  | int(11)     | YES  |     | NULL    |       |
| Emp_Dept    | varchar(20) | YES  |     | NULL    |       |
| Emp_PhoneNo | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)


mysql> SELECT *FROM employee;
+--------+----------+------------+------------+-------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept   | Emp_PhoneNo |
+--------+----------+------------+------------+-------------+
|    101 | Ram      |      52000 | R&D        | 8798654676  |
|    102 | Shyam    |      38000 | Finance    | 9898765687  |
|    103 | Anmol    |      61000 | Accounting | 9087864532  |
|    104 | Abhishek |      69000 | Purchasing | 7678987534  |
|    105 | Rohit    |      53000 | HRM        | 8897865643  |
+--------+----------+------------+------------+-------------+
5 rows in set (0.00 sec)

When we again apply the DESC command on ‘employee’ table just after applying the ALTER command written above, now we can see that Emp_City is not listed in the results. This shows that column named as Emp_City is now deleted from the employee table.

We have also used the SELECT command again. In the results of SELECT command, from all the records the values contained in Emp_City are removed.

2. Delete multiple columns from a table

To delete more than one column of the table, use the syntax given below:

ALTER TABLE TableName DROP COLUMN ColumnName1, DROP COLUMN ColumnName2,……ColumnNameN;

Here,

We need to specify all the column names which are to be removed, with DROP COLUMN clause.

Example:

Firstly, we will see the structure of the employee table and the records present in it.

mysql> DESC employee;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Emp_ID      | int(11)     | NO   | PRI | NULL    |       |
| Emp_Name    | varchar(20) | YES  |     | NULL    |       |
| Emp_Salary  | int(11)     | YES  |     | NULL    |       |
| Emp_Dept    | varchar(20) | YES  |     | NULL    |       |
| Emp_PhoneNo | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)


mysql> SELECT *FROM employee;
+--------+----------+------------+------------+-------------+
| Emp_ID | Emp_Name | Emp_Salary | Emp_Dept   | Emp_PhoneNo |
+--------+----------+------------+------------+-------------+
|    101 | Ram      |      52000 | R&D        | 8798654676  |
|    102 | Shyam    |      38000 | Finance    | 9898765687  |
|    103 | Anmol    |      61000 | Accounting | 9087864532  |
|    104 | Abhishek |      69000 | Purchasing | 7678987534  |
|    105 | Rohit    |      53000 | HRM        | 8897865643  |
+--------+----------+------------+------------+-------------+
5 rows in set (0.00 sec)

Here, we have selected the already created database with ‘USE employee_db’ command. “DESC employee” command describes the structure of ‘employee’ table. Then we have used the SELECT command to display the employee table created into employee_db.

Now, we will write a query to delete a column containing employee salary and employee phone number in ‘employee’ table.

mysql> ALTER TABLE employee DROP COLUMN Emp_Salary, DROP COLUMN Emp_PhoneNo;
Query OK, 5 rows affected (0.29 sec)
Records: 5  Duplicates: 0  Warnings: 0

ALTER TABLE command is used on employee table with DROP COLUMN clause on Emp_Salary and Emp_PhoneNo.

mysql> DESC employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Emp_ID   | int(11)     | NO   | PRI | NULL    |       |
| Emp_Name | varchar(20) | YES  |     | NULL    |       |
| Emp_Dept | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> SELECT *FROM employee;
+--------+----------+------------+
| Emp_ID | Emp_Name | Emp_Dept   |
+--------+----------+------------+
|    101 | Ram      | R&D        |
|    102 | Shyam    | Finance    |
|    103 | Anmol    | Accounting |
|    104 | Abhishek | Purchasing |
|    105 | Rohit    | HRM        |
+--------+----------+------------+
5 rows in set (0.00 sec)

When we again apply the DESC command on ‘employee’ table just after applying the ALTER command written above, we can see that Emp_Salary and Emp_PhoneNo is not listed in the results. This shows that column named as Emp_Salary and Emp_PhoneNo is now deleted from the employee table. Then we have also used the SELECT command again. In the results of SELECT command, from all the records the values contained in Emp_Salary and Emp_PhoneNo are removed.