CODEWITHSUNDEEP
sqlsql-serveralter-tablealter
Impostor
Impostor

Reputation: 2050

SQL Server : alter column with fix value

I have a table with this column

ALTER TABLE TestTable ADD TestColumn AS '1'

How do I change it to 2?

ALTER TABLE TestTable ALTER COLUMN TestColumn AS '2'

doesn't work.

Incorrect syntax near the keyword 'AS'.

Removing and adding the column is not an option.

Upvotes: 2

Views: 1628

Answers (2)

Paweł Dyl
Paweł Dyl

Reputation: 9143

Since it is constant column, you should recreate it:

ALTER TABLE TestTable DROP COLUMN TestColumn;
ALTER TABLE TestTable ADD TestColumn AS '2';

ALTER TABLE ... ALTER COLUMN ... syntax is not allowed with <computed_column_definition> - see specification.

ALTER COLUMN allows only following:

ALTER COLUMN column_name   
{   
    [ type_schema_name. ] type_name   
        [ (   
            {   
               precision [ , scale ]   
            }   
        ) ]   
    [ COLLATE collation_name ]   
    [ NULL | NOT NULL ] 
}

Upvotes: 6

Fahmi
Fahmi

Reputation: 37473

You may try this

alter table TestTable 
add default(2) for TestColumn

Upvotes: 0

Related Questions