Db2 ALTER TABLE DROP COLUMN By Practical Examples

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;

1 2	ALTER TABLE table_name DROP COLUMN column_name;

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
...;

ALTER TABLE table_name

DROP COLUMN column_name_1

DROP COLUMN column_name_2

...;

Note that there is no comma between the DROP COLUMN clauses.

We’ll use the orders table created in the ALTER TABLE tutorial for the demonstration.

The following example removes the note column from the orders table:

ALTER TABLE orders
DROP COLUMN note;

1 2	ALTER TABLE orders DROP COLUMN note;

To verify the change, you can use the DESCRIBE TABLE command:

DESCRIBE TABLE orders;

1	DESCRIBE TABLE orders;

Here is the output:

The note column has been dropped.

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;

ALTER TABLE orders

DROP COLUMN requested_date

DROP COLUMN order_status;

The following picture shows the column list of the orders table after executing the above statement:

DESCRIBE TABLE orders;

1	DESCRIBE TABLE orders;

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.