SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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.



ADVERTISEMENT
ADVERTISEMENT