Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Tables, views, and indexes » Computed columns

 

Altering a computed column

Change or remove the expression used in a computed column.

Prerequisites

You must be the owner of the table, or have one of the following privileges:

  • ALTER privilege on the table along with one of COMMENT ANY OBJECT, CREATE ANY OBJECT, or CREATE ANY TABLE system privileges
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege
 Task
  1. Connect to the database.

  2. Execute an ALTER TABLE statement similar to the following to change the expression used for a computed column:

    ALTER TABLE table-name
    ALTER column-name
    SET COMPUTE ( new-expression );
  3. To convert a column to a regular (non-computed) column, execute an ALTER TABLE statement similar to the following:

    ALTER TABLE
    table-name
    ALTER column-name
    DROP COMPUTE;

Results

In the case of changing the computation for the column, the column is recalculated when this statement is executed.

In the case of a computed column being changed to be a regular (non-computed) column, existing values in the column are not changed when the statement is executed, and are not automatically updated thereafter.

Example

Create a table named alter_compute_test, populate it with data, and run a select query on the table by executing the following statements:

CREATE TABLE alter_compute_test (
   c1 INT,
   c2 INT
) ;
INSERT INTO alter_compute_test (c1) VALUES(100);
SELECT * FROM alter_compute_test ;

Notice that column c2 returns a NULL value. Alter column c2 to become a computed column, populate the column with data, and run another SELECT statement on the alter_compute_test table.

ALTER TABLE alter_compute_test
   ALTER c2
   SET COMPUTE ( DAYS ( '2001-01-01' , CURRENT DATE ) )
INSERT INTO alter_compute_test (c1) VALUES(200) ;
SELECT * FROM alter_compute_test ;

The column c2 now contains the number of days since 2001-01-01. Next, alter column c2 so that it is no longer a computed column:

ALTER TABLE alter_compute_test
ALTER c2
DROP COMPUTE ;

 See also