Altering Tables ( Changing Columns )

The second way to alter a column definition is to use a CHANGE clause. The CHANGE clause enables the modification of both the column's definition and its name. To use this clause, specify the CHANGE keyword, followed by the column's existing name, its new name, and its new definition, in that order. Note that this means the existing name must be specified twice to change only the column definition (and not the name). For example, change the last_name column from CHAR(30) to CHAR(40) without renaming the column as follows:

ALTER TABLE head_of_state CHANGE last_name last_name CHAR(40) NOT NULL;

To change the name as well (for example, to Surname), provide the new name following the existing name:

ALTER TABLE head_of_state CHANGE last_name Surname CHAR(40) NOT NULL;

To change a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to change the last column.