ALTER TABLE
The ALTER statement modifies a table definition by adding or dropping columns.
Syntax
ALTER
TABLE
table {
ADD [COLUMN]
column column_def [,column column_def... ]
| DROP [COLUMN]
column [, column]}
Elements
column_def
data_type column_constraint
data_type
LONG | DOUBLE | TEXT [(size)] | CATEGORICAL [(size [,cat_elms])] | BOOLEAN | DATE | OBJECT | LEVEL
column_constraint
NULL | NOT NULL
cat_elms
size | {cat_name [, cat_name]}
size
For text columns, this is the maximum number of characters. For categorical columns, this is the maximum number of responses. For categories, this is the total number of categories, in which case the categories are automatically named columnN, where N is the category's position.
Notes
ALTER does not support the modification of the column data type. After a column has been created with a given type, the type cannot be changed.
Examples
These examples use the UNICOM Intelligence Data File version of the Museum sample data set, which is based on a survey of visitors to a museum. The files are called museum.mdd (which contains the metadata) and museum.ddf (which contains the case data), and by default both files are installed in:
[INSTALL_FOLDER]\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\
Before running the examples, use the Windows Explorer to make copies of the files and then make sure that the copies do
not have the Read-only attribute set. You can then run these examples using the DM Query sample tool, which is supplied with the UNICOM Intelligence Developer Documentation Library. To set up DM Query, see
How to run the example queries in DM Query using the museum sample; make sure that you select your copies of the museum sample files and not the original files.
1. Add new categorical column specifying category names
This example adds a new column to VDATA, and defines the column as single response categorical, with four categories called executive, skilled, unskilled, and other.
ALTER TABLE vdata
ADD sclass CATEGORICAL(1, {executive,skilled,unskilled,other})
2. Add new multiple response column
This example adds a new column to VDATA, and defines the column as multiple response categorical, and the maximum number of responses as 3. The new variable has 15 categories, called favorites1, favorites2, and so on, because the code does not specify names for the categories.
ALTER TABLE vdata
ADD favorites CATEGORICAL(3, 15)
3. Drop column
This example removes the sclass and favorites columns from VDATA:
ALTER TABLE vdata
DROP sclass, favorites
See also