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.