I have a table called "downloads" with a few thousand rows. I just added a column called is_completed using the following command:
我有一个名为“downloads”的表,有几千行。我刚刚使用以下命令添加了一个名为is_completed的列:
ALTER TABLE downloads ADD is_completed BIT default 1 NOT NULL
ALTER TABLE下载ADD is_completed BIT默认值1 NOT NULL
Now I would like to change the default value for is_completed to 0 - I tried this command to no avail:
现在我想将is_completed的默认值更改为0 - 我试过这个命令无济于事:
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.
这不起作用,它说我的语法接近默认值不正确。我似乎无法谷歌这为我的生活。有人知道正确的语法吗?我希望添加到此表中的所有未来行都将is_completed默认为0(如果省略显式值)。
4 个解决方案
#1
14
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
如果您需要更改数据类型或null / not null,那么您应该使用
alter table t modify c
#2
0
Drop the column and add again.
删除列并再次添加。
#3
0
In SQL Server, you would use ALTER TABLE... DROP CONSTRAINT, followed by ALTER TABLE... ADD CONSTRAINT. Presumably, Sybase would have something similar?
在SQL Server中,您将使用ALTER TABLE ... DROP CONSTRAINT,然后使用ALTER TABLE ... ADD CONSTRAINT。据推测,Sybase会有类似的东西吗?
#4
0
1) pull the PKs of all the rows WHERE is_completed is = 1 into another table or do something like:
1)将所有行的PKs(其中is_completed = 1)拉入另一个表中或执行以下操作:
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
保存此输出,以便稍后运行它,如果您的原始表只有几千行,那么这不应该那么大2)删除列3)添加具有您现在想要的默认值的列4)运行保存的输出从上面的查询,或UPDATE下载表与连接到用于存储is_completed = 1的行的表的连接
#1
14
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
如果您需要更改数据类型或null / not null,那么您应该使用
alter table t modify c
#2
0
Drop the column and add again.
删除列并再次添加。
#3
0
In SQL Server, you would use ALTER TABLE... DROP CONSTRAINT, followed by ALTER TABLE... ADD CONSTRAINT. Presumably, Sybase would have something similar?
在SQL Server中,您将使用ALTER TABLE ... DROP CONSTRAINT,然后使用ALTER TABLE ... ADD CONSTRAINT。据推测,Sybase会有类似的东西吗?
#4
0
1) pull the PKs of all the rows WHERE is_completed is = 1 into another table or do something like:
1)将所有行的PKs(其中is_completed = 1)拉入另一个表中或执行以下操作:
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
保存此输出,以便稍后运行它,如果您的原始表只有几千行,那么这不应该那么大2)删除列3)添加具有您现在想要的默认值的列4)运行保存的输出从上面的查询,或UPDATE下载表与连接到用于存储is_completed = 1的行的表的连接