Summary: in this tutorial, you’ll learn how to drop one or more columns in a table by using the Db2 ALTER TABLE DROP COLUMN
statement.
Introduction to Db2 ALTER TABLE DROP COLUMN
statement
Sometimes, you may want to delete one or more unused columns from a table. To do this, you use the ALTER TABLE DROP COLUMN
statement as follows:
ALTER TABLE table_name
DROP COLUMN column_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, specify the name of the table from which you want to drop the column in the
ALTER TABLE
clause. - Second, specify the name of the column that you want to delete in the
DROP COLUMN
clause.
To delete multiple columns, you can use the following syntax:
ALTER TABLE table_name
DROP COLUMN column_name_1
DROP COLUMN column_name_2
...;
Code language: SQL (Structured Query Language) (sql)
Note that there is no comma between the DROP COLUMN
clauses.
Db2 ALTER TABLE DROP COLUMN
examples
We’ll use the orders
table created in the ALTER TABLE
tutorial for the demonstration.
1) Dropping one column example
The following example removes the note
column from the orders
table:
ALTER TABLE orders
DROP COLUMN note;
Code language: SQL (Structured Query Language) (sql)
To verify the change, you can use the DESCRIBE TABLE
command:
DESCRIBE TABLE orders;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
The note column has been dropped.
2) Dropping multiple columns example
This example uses the ALTER TABLE DROP COLUMN
to drop the requested_date
and order_status
columns:
ALTER TABLE orders
DROP COLUMN requested_date
DROP COLUMN order_status;
Code language: SQL (Structured Query Language) (sql)
The following picture shows the column list of the orders table after executing the above statement:
DESCRIBE TABLE orders;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you’ve learned how to drop one or more unused columns from a table by using the Db2 ALTER TABLE DROP COLUMN
statement.