0 votes
1 view
in SQL by (20.3k points)

I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement.

From MSDN's ALTER TABLE documentation...

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed.

It says that multiple columns can be listed in the the statement but the syntax doesn't show an optional comma or anything that would even hint at the syntax.

How should I write my SQL to drop multiple columns in one statement (if possible)

1 Answer

0 votes
by (36.7k points)
edited by

For SQL Server:

alter table Table_Name

drop column ColumnA, ColumnB

Want to learn SQL from scratch? Here's is the right video for you on SQL provided by Intellipaat

The syntax is as follows:

DROP {[ CONSTRAINT] constraint_name | COLUMN col} [ ,...n ] 

For MySQL, you can use this:

alter table Table_Name

drop ColumnA, drop ColumnB

...