Teradata ALTER TABLE MODIFY COLUMN

Teradata ALTER TABLE modify column is used to modify the column datatype, length, or format. The option for changing the length of a datatype is limited in Teradata. However, we can use the below syntax for changing the length of the datatype only for varchar.

Syntax

ALTER TABLE database_name.tbl_name 
ADD existing_column_name new_data_type (New Length)

Example

Suppose we have an employee table with the below definition.

show table Teradatapoint.employee;

CREATE SET TABLE Teradatapoint.employee ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
empid INTEGER,
empname VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
hobbies VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'Listen to Music',
native_place VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
)
UNIQUE PRIMARY INDEX ( empid );

Now if you want to change the length of empname to varchar(100), you can use the following statement. 

ALTER TABLE Teradatapoint.employee ADD empname varchar(100);

Now, you can verify the table definition as below.

show table Teradatapoint.employee;

CREATE SET TABLE Teradatapoint.employee , NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
empid INTEGER,
empname VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(50)',hobbies VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'Listen to Music',
native_place VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
)
UNIQUE PRIMARY INDEX ( empid );

To modify the datatype from char to int or to decrease the size of the varchar column, the table needs to be dropped and recreated.

Let’s try to decrease the size of the empname now again to varchar(50).

ALTER TABLE Teradatapoint.employee ADD empname varchar(50); -- Syntax error 3558

The below are the unsuccessful cases.

1. CHAR (From CHAR(20) to CHAR(25)) — Syntax Error 3558
2. TIMESTAMP (From TIMESTAMP(0) to TIMESTAMP(1)) — Syntax Error 3558
3. DECIMAL (From DECIMAL(10,0) to DECIMAL(10,1)) — Syntax Error 3558
4. INTEGER (From INTEGER to DECIMAL(10,0)) — Syntax Error 3558