SQL DROP COLUMN

Summary: in this tutorial, you will learn how to use the SQL DROP COLUMN clause to remove one or more columns from an existing table.

Introduction to SQL DROP COLUMN statement #

Sometimes, you may want to drop one or more unused column from an existing table. To do so, you use the ALTER TABLE as follows:

ALTER TABLE table_name
DROP COLUMN column_name1,
[DROP COLUMN column_name2];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• table_name is the name of the table which contains the columns that you are removing.
• column_name1, column_name2 are the columns that you are dropping.

The above syntax is supported by MySQL and PostgreSQL.

Oracle and SQL Server have a slightly different syntax:

ALTER TABLE table_name
  DROP COLUMN  
    column_name1, 
    [column_name2];
Code language: SQL (Structured Query Language) (sql)

SQL DROP COLUMN examples #

The following statement creates a new table named persons for the demonstration:

CREATE TABLE persons (
    person_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255)
);
Code language: SQL (Structured Query Language) (sql)

A) Dropping one column example #

The following statement drops the email column from the persons table:

ALTER TABLE persons
DROP COLUMN email;
Code language: SQL (Structured Query Language) (sql)

B) Dropping multiple columns example #

The following statement drops the date_of_birth and phone columns:

ALTER TABLE persons
DROP COLUMN date_of_birth,
DROP COLUMN phone;
Code language: SQL (Structured Query Language) (sql)

This statement works in MySQL and PostgreSQL.

For Oracle and SQL Server, you use the following statement:

ALTER TABLE persons
    DROP COLUMN 
        date_of_birth,
        phone;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL DROP COLUMN statement to remove one or more columns from a table.