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

Question

I have a table called "downloads" with a few 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, it says I have incorrect syntax near default. I can't seem to google this for the life of me. Anyone know the correct syntax? I want all future rows added to this table to have is_completed default to 0 if an explicit value is omitted.

Answer 1

Drop the column and add again.

Answer 2

In SQL Server, you would use ALTER TABLE... DROP CONSTRAINT, followed by ALTER TABLE... ADD CONSTRAINT. Presumably, Sybase would have something similar?

Answer 3

1) pull the PKs of all the rows WHERE is_completed is = 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 you can run it later, if your original table only has a few thousand rows then this shouldn't be that large
2) drop the column
3) add the column with the default you now want
4) run the saved output from the above query, or UPDATE the downloads table with a join to the table used to store the rows where is_completed is = 1

Answer 4

To alter a default you need to use replace rather than modify:

alter table downloads replace is_completed default 0

if you need to change the data type or the null/not null then you should use alter table t modify c