Teradata - Alter Tables



ALTER TABLE command is used to add or drop columns from an existing table. You can also use ALTER TABLE command to modify the attributes of the existing columns.

Syntax

Following is the generic syntax for ALTER TABLE.

ALTER TABLE <tablename> 
ADD <columnname> <column attributes> 
DROP <columnname>;

Example

The following example drops the column DOB and adds a new column BirthDate.

ALTER TABLE employee 
ADD BirthDate DATE FORMAT 'YYYY-MM-DD', 
DROP DOB;

You can run SHOW TABLE command to view the changes to the table. In the following output, you can see that the column employee_dob is removed and BirthDate is added.

SHOW table employee;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
---------------------
CREATE SET TABLE Employee ,FALLBACK, 
   NO BEFORE JOURNAL, 
   NO AFTER JOURNAL, 
   CHECKSUM = DEFAULT, 
   
   DEFAULT MERGEBLOCKRATIO ( 
      EmployeeNo INTEGER, 
      FirstName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, 
      LastName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC, 
      JoinedDate DATE FORMAT 'YYYY-MM-DD', 
      DepartmentNo BYTEINT,
      BirthDate DATE FORMAT 'YYYY-MM-DD'
   ) 
UNIQUE PRIMARY INDEX ( EmployeeNo );
teradata_tables.htm
Advertisements