You can execute the ALTER TABLE statement to add or delete an attribute column in an existing mapping table.

Note For more information about how to create a mapping table for an existing table or search index, see Create mapping tables for tables.

Usage notes

  • You can execute the ALTER TABLE statement to update the schema of a mapping table. The schema of the Tablestore table for which the mapping table is created is not updated.
  • You cannot add or delete a primary key column in a mapping table.
  • After you execute the ALTER TABLE statement, the SQL engine asynchronously refreshes the mapping table. The refresh takes up to 30 seconds. During the refresh period, the column that you added may not be returned when you perform the operations that are supposed to return all columns.
  • You can execute the ALTER TABLE statement to update an attribute column only in a mapping table that is created by executing the CREATE TABLE statement. You cannot execute the ALTER TABLE statement to update an attribute column in a mapping table that is automatically created for a table by executing the DESCRIBE statement.
  • You can add or delete only one attribute column in a mapping table by executing the ALTER TABLE statement. If you want to add or delete multiple attribute columns in a mapping table, you can execute the ALTER TABLE statement multiple times.

Syntax

ALTER TABLE table_name option column_name [data_type];

Parameters

Parameter Required Description
table_name Yes The name of the mapping table, which is used to identify the mapping table.
option Yes The update operation that you want to perform. Valid values:
  • ADD COLUMN: adds an attribute column to the mapping table.
  • DROP COLUMN: deletes an attribute column from the mapping table.
column_name Yes The name of the attribute column. The name of the attribute column that you want to add must be different from the names of existing attribute columns in the mapping table.

The column name in SQL must be equivalent to the column name in the Tablestore table. For example, if the column name in the Tablestore table is Aa, the column name in SQL must be Aa, AA, aA, or aa.

data_type No The data type of the attribute column, such as BIGINT, DOUBLE, or BOOL. This parameter is required only if you set option to ADD COLUMN.

The data type of the column in SQL must match the data type of the column in the Tablestore table. For more information about data type mappings, see Data type mappings.

Examples

  • You can execute the following statements to add the colvalue attribute column and delete the content attribute column in the exampletable mapping table. The data type of the colvalue attribute column is BIGINT and the data type of the content attribute column is MEDIUMTEXT. 
    ALTER TABLE exampletable ADD COLUMN colvalue BIGINT;
    ALTER TABLE exampletable ADD COLUMN content MEDIUMTEXT;
  • You can execute the following statement to delete the colvalue attribute column from the exampletable mapping table. The data type of the colvalue attribute column is BIGINT. 
    ALTER TABLE exampletable DROP COLUMN colvalue;