Teradata ALTER TABLE DROP COLUMN statement drops an existing from a Teradata table. Multiple columns also can be dropped in a single statement using Teradata ALTER TABLE DROP COLUMN.
You must have DROP privileges on the table for dropping columns in Teradata.
Teradata ALTER TABLE DROP COLUMN syntax
The syntax for dropping a single column from a Teradata table is as below.
ALTER TABLE database_name.table_name DROP column_name;
The syntax for dropping multiple columns in a single statement is as below.
ALTER TABLE database_name.table_name DROP column_name1, DROP column_name2 .... DROP column_name(n);
Here,
- database_name – The name of the database which contains the table.
- table_name – The name of the table from which you want to drop the column.
- column_name(n) – The name of the column(s) which you want to drop.
Teradata ALTER TABLE DROP COLUMN example
Let’s understand the concept of the Teradata DROP COLUMN statement using some examples.
For this, we will consider the table Employee in which we have added 3 new columns names SALARY, DESIGNATION, and DEPTNO in our previous tutorial ALTER TABLE ADD COLUMN.
1) Dropping a Single Column
The following statement drops a single column SALARY from the Employee table.
ALTER TABLE TUTORIALSBOOK.Employee DROP SALARY; *** Table has been modified. *** Total elapsed time was 1 second.
Now let’s check the table Employee. As you can see the SALARY column has been deleted from the table.
SELECT * FROM TUTORIALSBOOK.Employee;
*** Query completed. 8 rows found. 6 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME HIREDATE DESIGNATION DEPTNO
----------- ------------------------- ------------------------- ---------- -------------------------------------------------- -----------
1008 JOHN SMITH 2005-10-12 ? 0
1004 JONES NICK 2004-08-19 ? 0
1006 ISSAC STEFAN 2002-11-18 ? 0
1002 DIANA LORANCE 2005-01-05 ? 0
1007 NANCY GILBERT 2003-04-06 ? 0
1003 JAMES MADISON 2005-09-18 ? 0
1005 LUCY GILLER 2006-04-01 ? 0
1001 STEFAN SALVATORE 2001-04-12 ? 0
2) Dropping Multiple Columns
Multiple columns also can be dropped from a Teradata table using a single ALTER TABLE statement. The following statement deletes both DESIGNATION and DEPTNO columns from the Employee table.
ALTER TABLE TUTORIALSBOOK.Employee DROP DESIGNATION, DROP DEPTNO; *** Table has been modified. *** Total elapsed time was 1 second.
Let’s check the Employee table again. As you can see that both the columns DESIGNATION and DEPTNO have been deleted from the table.
SELECT * FROM TUTORIALSBOOK.Employee;
*** Query completed. 8 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME HIREDATE
----------- ------------------------- ------------------------- ----------
1008 JOHN SMITH 2005-10-12
1004 JONES NICK 2004-08-19
1006 ISSAC STEFAN 2002-11-18
1002 DIANA LORANCE 2005-01-05
1007 NANCY GILBERT 2003-04-06
1003 JAMES MADISON 2005-09-18
1005 LUCY GILLER 2006-04-01
1001 STEFAN SALVATORE 2001-04-12