Teradata ALTER TABLE MODIFY COLUMN

Teradata ALTER TABLE MODIFY COLUMN modified the column attributes like data type, length, format, default value, etc. Some limited options are available for changing the length of data type in Teradata. However, you can use the following syntax for changing the length of the data type.

Teradata ALTER TABLE MODIFY COLUMN syntax

The syntax of Teradata ALTER TABLE MODIFY COLUMN is as follows:

ALTER TABLE database_name.table_name
ADD existing_column_name new_data_type new_length;

Teradata ALTER TABLE MODIFY COLUMN example

Let’s understand the concept of the Teradata ALTER TABLE MODIFY COLUMN with the help of an example.

We will create a student table first to demonstrate the concept.

CREATE SET TABLE TUTORIALSBOOK.Student
(
ROLLNO INTEGER,
FIRSTNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
LASTNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
COURSE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)
UNIQUE PRIMARY INDEX ( ROLLNO );

*** Table has been created.
*** Total elapsed time was 1 second.

Second, insert some data into the table.

INSERT INTO TUTORIALSBOOK.Student VALUES (01,'Pritam','Roy','Comp. Sci.');
INSERT INTO TUTORIALSBOOK.Student VALUES (02,'Rahul','Haldar','IT');
INSERT INTO TUTORIALSBOOK.Student VALUES (03,'Rekha','Singh','IT');

Suppose, you want to change the length of the FIRSTNAME column to varchar(50), you can use the following syntax.

ALTER TABLE TUTORIALSBOOK.Student ADD FIRSTNAME varchar(50);

*** Table has been modified.
*** Total elapsed time was 1 second.

You can now check the definition of the table using the show table statement.

SHOW TABLE TUTORIALSBOOK.Student;

 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.

---------------------------------------------------------------------------
CREATE SET TABLE TUTORIALSBOOK.Student ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      ROLLNO INTEGER,
      FIRSTNAME VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X
(30)',
      LASTNAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      COURSE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( ROLLNO );

You can notice that the length of the column FIRSTNAME has been modified to varchar(50).

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 FIRSTANME now again to varchar(30).

ALTER TABLE TUTORIALSBOOK.Student ADD FIRSTNAME varchar(30);
*** Failure 3558 Cannot alter the specified attribute(s) for FIRSTNAME.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

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