SQL > ALTER TABLE > Drop Column Syntax

Sometimes we will wish to delete a column from an existing table in SQL. To do this, we specify that we want to change the table structure via the ALTER TABLE command, followed by a specification indicating that we want to remove a column. The detailed syntax for each database is as follow:

ALTER TABLE DROP COLUMN permanently removes a column and all its data from a table. The COLUMN keyword is optional in MySQL but required in Oracle, SQL Server, and BigQuery — always back up data before dropping columns.

In MySQL, the syntax for ALTER TABLE Drop Column is, 

ALTER TABLE "table_name"
DROP "column_name";

In Oracle, SQL Server, and Google BigQuery, the syntax for ALTER TABLE Drop Column is, 

ALTER TABLE "table_name"
DROP COLUMN "column_name";

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

Our goal is to drop the "Birth_Date" column. To do this, we key in:

MySQL: 

ALTER TABLE Customer DROP Birth_Date;

SQL Server: 

ALTER TABLE Customer DROP COLUMN Birth_Date;

Oracle: 

ALTER TABLE Customer DROP COLUMN Birth_Date;

Google BigQuery: 

ALTER TABLE Customer DROP COLUMN Birth_Date;

The resulting table structure is:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 

Frequently Asked Questions

Q: Can I recover data after dropping a column?
A: No. Dropping a column permanently removes the column and all its data. Always back up your data before dropping columns in a production database.
Q: What is the difference between DROP and DROP COLUMN in MySQL?
A: In MySQL, both DROP column_name and DROP COLUMN column_name are valid. The COLUMN keyword is optional in MySQL but required in Oracle, SQL Server, and BigQuery.
Q: Can I drop multiple columns at once?
A: In MySQL, you can drop multiple columns in one statement: ALTER TABLE Customer DROP COLUMN Birth_Date, DROP COLUMN Country;. Other databases may require separate statements.
Q: What happens if a dropped column has an index or constraint on it?
A: In most databases, dropping a column that is part of an index or constraint will also drop that index or constraint automatically. Always verify dependent objects before dropping a column.

Related Topics

Next: SQL ADD INDEX

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact