How to Drop a Column if It Exists in MySQL

In today’s post, we’ll learn how to drop single or multiple columns if it exists in MySQL.

Drop a Column if It Exists in MySQL

In a table, a column is a row of cells that can include text, numbers, and graphics. For each row in a table, a value is stored in each column.

We may wish to delete single or numerous columns from a table. A table’s columns can be added, modified, or dropped/deleted using the MySQL ALTER TABLE command.

When columns are eliminated from a table, they are also deleted from any indexes they were a part of. An index is also erased if all the columns that make it up are removed.

The IF EXISTS clause is used only for eliminating databases, tables, and views. When utilizing IF EXISTS, if the object we attempt to drop does not exist, the execution is interrupted as soon as MySQL notices the absence of the entity and sends a warning.

The system schema is the one used by MySQL. It includes tables and columns containing the data needed by the running MySQL server.

The MySQL schema is broadly categorized as system tables for general operational uses and data dictionary tables for storing database item metadata. The COLUMNS table contains details on table columns.

Syntax:

DROP COLUMN column_name ON table_name;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name DROP COLUMN column_name_1, DROP COLUMN column_name_2;
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = table_name AND COLUMN_NAME = column_name

Here, column_name is the name of the column you wish to delete, and table_name is the name of the table from which the column should be deleted. Because the term COLUMN in the DROP COLUMN clause is optional, you can use the following shorter statement:

ALTER TABLE table_name DROP column_name;

The second last phrase demonstrates how, if you separate the operations with commas, you may conduct numerous drop actions with a single ALTER TABLE query.

To further understand the previous concept, consider the following example:

DROP COLUMN email ON Employees;
ALTER TABLE Employees DROP COLUMN email;
IF EXISTS (SELECT 1
    FROM   INFORMATION_SCHEMA.COLUMNS
        WHERE  TABLE_NAME = 'Employees'
        AND COLUMN_NAME = 'email')
    BEGIN
        ALTER TABLE Employees
        DROP COLUMN email
    END
GO

We are removing the Employees table’s email column in the above example. This will delete the email column, any data saved, and any associated indexes.

If you want to ensure that the email column is in the Employees table, use the last command, which checks it from INFORMATION_SCHEMA and deletes if it returns true.

Run the above line of code in any browser compatible with MySQL. It will display the following outcome.

Output:

Query executed successfully.