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:

1. Adding a new, temporary column to the table that mirrors the current metadata of the column to be changed.
2. Copying the data from the column to be changed to the newly created temporary column.
3. Modifying the column.
4. Copying data from the temporary column to the redefined column.
5. Dropping the temporary column.

An Example:

Suppose the 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:

1. First, create a temporary column to hold the data in OFFICE_NO during the modification process:

   EXEC SQL
   ALTER TABLE EMPLOYEE ADD TEMP_NO CHAR(3);
   EXEC SQL
   COMMIT;
   

2. Move existing data from OFFICE_NO to TEMP_NO to preserve it:

   EXEC SQL
   UPDATE EMPLOYEE
   SET TEMP_NO = OFFICE_NO;
   

3. 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;
   

4. Move the data from TEMP_NO to OFFICE_NO:

   EXEC SQL
   UPDATE EMPLOYEE
   SET OFFICE_NO = TEMP_NO;
   

5. Finally, drop the TEMP_NO column:

   EXEC SQL
   ALTER TABLE DROP TEMP_NO;
   EXEC SQL
   COMMIT;
   

For more information about dropping column definitions, see Dropping an Existing Column. For more information about adding column definitions, see Modifying a Column.

The 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 EMPLOYEE table:

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;

Important:
Any changes to the field definitions may require the indexes to be rebuilt.

For the complete syntax of ALTER TABLE, see the Language Reference Guide.

Advance To:

• Altering a View