改变列:null 到 not null

2021-11-27 00:00:00 null tsql sql-server alter-table alter-column

我有一个表，其中有几个可为空的整数列.由于多种原因，这是不可取的，因此我希望将所有空值更新为 0，然后将这些列设置为 NOT NULL.除了将空值更改为 0 之外，还必须保留数据.

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved.

我正在寻找特定的 SQL 语法来将列(称为 ColumnA)更改为not null".假设数据已更新为不包含空值.

I am looking for the specific SQL syntax to alter a column (call it ColumnA) to "not null". Assume the data has been updated to not contain nulls.

使用SQL server 2000.

推荐答案

首先，让所有当前的 NULL 值消失:

First, make all current NULL values disappear:

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

然后，更新表定义以禁止NULL":

Then, update the table definition to disallow "NULLs":

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

相关文章