Geek Answers Handbook

SybaseDB, change the default value of an existing column in a table

I have a table called "downloads" with several thousand rows. I just added a column called is_completed using the following command:

ALTER TABLE downloads ADD is_completed BIT default 1 NOT NULL

Now I would like to change the default value for is_completed to 0 - I tried this command to no avail:

 ALTER TABLE downloads MODIFY is_completed default 0

This does not work, he says that I have the wrong syntax next to the default. I can't seem to do this for the life of me. Does anyone know the correct syntax? I want all future rows added to this table to default to 0 if an explicit value is omitted.

+6
sql database sybase default
source share
4 answers

To change the default value, you need to use a replacement, not change:

 alter table downloads replace is_completed default 0

If you need to change the data type or null / not null, you should use

 alter table t modify c
+13
source share

Drop the column and add again.

0
source share

In SQL Server, you should use ALTER TABLE ... DROP CONSTRAINT and then ALTER TABLE ... ADD CONSTRAINT. Presumably Sybase will have something like this?

0
source share

1) pull PK of all rows WHERE is_completed = 1 into another table or do something like:

 SELECT 'UPDATE downloads SET is_completed is = 1 WHERE PK='+CONVERT(varchar(10),PK) FROM downloads

save this output so that you can run it later if your source table has a few thousand rows , then it should not be big 2) drop the column 3) add the default column that you need now 4) run the saved output from the above query or UPDATE the download table with the connection to the table used to store the rows, where is_completed = 1

0
source share

More articles:


All Articles