Modifying a Column
Go Up to Altering a Table
An existing column definition can be modified using
ALTER TABLE, but if data already stored in that column is not preserved before making changes, it will be lost.
Preserving data entered in a column and modifying the definition for a column, is a five-step process:
- Adding a new, temporary column to the table that mirrors the current metadata of the column to be changed.
- Copying the data from the column to be changed to the newly created temporary column.
- Modifying the column.
- Copying data from the temporary column to the redefined column.
- Dropping the temporary column.
EMPLOYEE table contains a column,
OFFICE_NO, defined to hold a data type of
CHAR(3), and suppose that the size of the column needs to be increased by one. The following numbered sequence describes each step and provides sample code:
- First, create a temporary column to hold the data in
OFFICE_NOduring the modification process:
EXEC SQL ALTER TABLE EMPLOYEE ADD TEMP_NO CHAR(3); EXEC SQL COMMIT;
- Move existing data from
TEMP_NOto preserve it:
EXEC SQL UPDATE EMPLOYEE SET TEMP_NO = OFFICE_NO;
- Modify the new column definition for
OFFICE_NO, specifying the data type and new size:
EXEC SQL ALTER TABLE EMPLOYEE ALTER OFFICE_NO TYPE CHAR(4); EXEC SQL COMMIT;
- Move the data from
EXEC SQL UPDATE EMPLOYEE SET OFFICE_NO = TEMP_NO;
- Finally, drop the
EXEC SQL ALTER TABLE DROP TEMP_NO; EXEC SQL COMMIT;
ALTER TABLE ALTER command allows you to change the column position and name as well.
For example, the following statement moves a column,
EMP_NO, from the third position to the second position in the
ALTER TABLE EMPLOYEE ALTER EMP_NO POSITION 2;
You could also change the name of the
EMP_NO column to
EMP_NUM as in the following example:
ALTER TABLE EMPLOYEE ALTER EMP_NO TO EMP_NUM;
For the complete syntax of
ALTER TABLE, see the Language Reference Guide.