Alter Data type for existing column in a table

Database
Enthusiast

Alter Data type for existing column in a table

In teradata

is there any other way  to alter the column data type of table

 

procedure we follow

create a temp table with old table ddl

insert the data into temp from old

create the new table with modifications of old table

insert back data into new table

drop temp table

 

i dont know why teradata doesnot have alter to data type without dropping the table.

 


Accepted Solutions
Junior Supporter

Re: Alter Data type for existing column in a table

Hi.

 

There is another method.

 

ALTER TABLE ADD COLUMN NEW_COL with DATATYPE desired.

UPDATE TABLE SET NEW_COL = CAST(OLD_COL AS whatever).

ALTER TABLE DROP OLD_COL.

ALTER TABLE RENAME NEW_COL AS OLD_COL.

 

Cheers.

Carlos.

1 ACCEPTED SOLUTION
5 REPLIES
Junior Supporter

Re: Alter Data type for existing column in a table

Hi.

 

There is another method.

 

ALTER TABLE ADD COLUMN NEW_COL with DATATYPE desired.

UPDATE TABLE SET NEW_COL = CAST(OLD_COL AS whatever).

ALTER TABLE DROP OLD_COL.

ALTER TABLE RENAME NEW_COL AS OLD_COL.

 

Cheers.

Carlos.

Junior Contributor

Re: Alter Data type for existing column in a table

There are a lot of restrictions for altering a data type, it's only allowed in cases where the internal storage will not be changed.

Besides Carlos way you can simplify your current approach:

 

  1. create a new table with the new data type 
  2. MERGE the data from old to new, this avoids an extra spool step (if the PI is the same)
  3. DROP the old and RENAME the new table.
Junior Supporter

Re: Alter Data type for existing column in a table

Hi Dieter.

I try to avoid creating a new table if possible, thus preserving the objectID, rights, statistics, etc... And saving space too ;-)

Cheers.

Carlos.

Enthusiast

Re: Alter Data type for existing column in a table

thanks dnoeth...

i follow your posts on other Teradata forums too..Thanks for helping...

Enthusiast

Re: Alter Data type for existing column in a table

Thanks Carlos...