JIYIK CN >

In this article, we will learn how to delete single or multiple columns present in MySQL.

If the column exists in MySQL, then delete the column

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

We may want to remove single or multiple columns from a table. ALTER TABLEColumns of a table can be added, modified or deleted/removed using MySQL commands.

When columns are dropped from a table, they are also dropped from any indexes they were a part of. If you drop all the columns that make up an index, the index is also dropped.

The IF EXISTS clause is used only for deleting databases, tables, and views. When using IF EXISTS, if the object we are trying to delete does not exist, the execution will be interrupted as soon as MySQL notices that the entity does not exist and sends a warning.

The system schema is the schema that MySQL uses. It includes the tables and columns that contain the data needed by a running MySQL server.

The MySQL schema is broadly divided into system tables for general operational purposes and data dictionary tables for storing metadata about database items. The COLUMNS table contains detailed information about table columns.

grammar:

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 to be dropped, and table_name is the name of the table from which the column is to be dropped. 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-to-last phrase demonstrates how you can ALTER TABLEperform a large number of delete operations with a single query if you separate the operations with commas.

To further understand the previous concepts, 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

In the above example, we are deleting the Email column of the Employees table. This will delete the Email column, any saved data, and any associated indexes.

If you want to make sure that the Email column is in the Employees table, use the last command, which INFORMATION_SCHEMAchecks for it and deletes if it returns true.

Run the above lines of code in any MySQL compatible browser. It will display the following result.

Output:

Query executed successfully.