Logo
×

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)
How to delete column in table

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
How to delete column in table

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)
How to delete column in table

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)
How to delete column in table

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
How to delete column in table

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)
How to delete column in table

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.

Related Topics

SQL SELECT WHERE Clause

In this SQL section, we will help you understand the concept of the SQL SELECT WHERE clause with a few examples. The WHERE clause in SELECT query displays those records as...

5 minutes read.

SQL SELECT Statement

The SQL SELECT statement is used to retrieve the data from the tables. We can also retrieve the selected records from the table using the query condition. The SQL SELECT...

5 minutes read.

SQL SELECT MIN

The SQL Min() function is an aggregate function in SQL. SQL Min() returns the minimum value of a given condition. The expression may be numerical, or it may be an expression. The syntax...

5 minutes read.

How to create a database in SQL?

How to create a database in SQL It is very necessary to create a database in order to store the data into the database.Database name must always be unique.SQL does not...

6 minutes read.

SQL COPY Table

In this tutorial, we will learn how to copy tables in SQL with the help of examples. Using the SELECT INTO statement in the SQL we can copy one table into...

4 minutes read.

SQL Handling Duplicate

Removing Duplicates using DISTINCT Keyword: By using the DISTINCT keyword in SQL we can remove duplicate characters from tables or databases. A table contains more duplicate values and duplicate values can cause...

4 minutes read.

Where Condition in SQL

WHERE clause is used with the Data Manipulation Language Command in SQL, the Data Manipulation Language commands are the SELECT statement, UPDATE statement, and DELETE statement. WHERE clause condition is an...

5 minutes read.

SQL DROP Table

In this tutorial, we will help you to understand how to delete the table from the database in SQL with the help of examples. The DROP TABLE query is used to...

4 minutes read.

SQL Cast Operator

While writing SQL queries, we can see many values which are to be converted into another datatype for accessing them. In SQL this conversion is generally done by CAST function. CAST...

4 minutes read.

How to use INNER JOIN in SQL

In this article, we will learn about the INNER JOIN concept and how to use it in SQL with the WHERE clause. What is INNER JOIN in SQL? Inner Join is a...

6 minutes read.

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

4 minutes read.

Trigger in SQL

In this article, we will learn about the concept of trigger in SQL and its implementation with the help of an example. A Trigger in Structured Query Language is a set...

4 minutes read.

SQL Left Join

The SQL Left Join query displays all the records from the table and displays similar records from the right table. The query displays zero records if it doesn’t find any...

4 minutes read.

How to use GROUP BY clause in SQL

In this SQL article, we will learn about the GROUP BY clause and how to use it in SQL. We will also discuss using the GROUP BY clause with the...

6 minutes read.

SQL HAVING Clause

In this tutorial, we will understand the HAVING clause concept in SQL with the help of examples. The HAVING Clause in the SQL is used as we cannot use the WHERE...

3 minutes read.

SQL INSERT INTO Values

This article will help you in getting a better understanding of a very important SQL INSERT INTO VALUES function. INSERT INTO statement is used to insert or add a new record...

4 minutes read.

SQL SELECT OR Operator

This SQL tutorial explains and helps us understand how to use the OR operator in the SELECT query with examples. The OR operator in the Structured Query Language is used to...

3 minutes read.

SQL DELETE

In this tutorial, you will learn about the SQL DELETE concept by using examples. In Structured Query Language (SQL), we fetch the data from the database table using SELECT Statement and...

3 minutes read.

SQL SELECT BETWEEN Operator

This SQL tutorial explains and helps us understand how to use the BETWEEN operator in the SELECT query with examples. The SQL SELECT BETWEEN operator retrieves the data from the table...

2 minutes read.

SQL NOT Operator

SQL NOT is a Boolean operator used with the WHERE clause. NOT operator shows the records if the expression is false. When we use the NOT operator, we fetch only...

7 minutes read.