在SQL中，如何在现有表中添加新列后添加值？

I created a table and inserted 3 rows. Then I added a new column using alter. How can I add values to the column without using any null values?

我创建了一个表并插入了3行。然后我使用alter添加了一个新列。如何在不使用任何空值的情况下向列添加值?

5 个解决方案

#1

Two solutions.

Provide a default value for the column. This value will be used initially for all existing rows. The exact syntax depends on your database, but will will usually look like ..

为列提供默认值。该值最初将用于所有现有行。确切的语法取决于您的数据库,但通常看起来像..

this:

ALTER TABLE YourTable
ADD YourNewColumn INT NOT NULL
DEFAULT 10 
WITH VALUES;

Add the column with null values first. Then update all rows to enter the values you want.

首先添加具有空值的列。然后更新所有行以输入所需的值。

Like so:

ALTER TABLE YourTable
ADD YourNewColumn INT NULL;

UPDATE YourTable SET YourNewColumn = 10; -- Or some more complex expression

Then, if you need to, alter the column to make it not null:

然后,如果需要,请更改列以使其不为null:

ALTER TABLE YourTable ALTER COLUMN YourNewColumn NOT NULL;

#2

Why don't you use UPDATE statement:

为什么不使用UPDATE语句:

UPDATE tablename SET column=value <WHERE ...>

WHERE is optional. For instance in T-SQL for table:

WHERE是可选的。例如在T-SQL for table中:

I can update column NewTestColumn by this statement:

我可以通过以下语句更新NewTestColumn列:

UPDATE [dbo].[Table] SET [NewTestColumn] = 'Some value'

#3

I think below SQL useful to you

我认为下面的SQL对你有用

update table_name set newly_added_column_name = value;

#4

Suppose you have a Employee table with these columns Employee_ID, Emp_Name,Emp_Email initially. Later you decide to add Emp_Department column to this table. To enter values to this column, you can use the following query :

假设您最初有一个Employee表,其中包含Employee_ID,Emp_Name,Emp_Email这些列。稍后您决定将Emp_Department列添加到此表中。要为此列输入值,可以使用以下查询:

Update *Table_Name* set *NewlyAddedColumnName*=Value where *Columname(primary key column)*=value

Example update TblEmployee set Emp_Department='Marketing' where Emp_ID='101'

示例更新TblEmployee设置Emp_Department ='Marketing',其中Emp_ID ='101'

#5

   update table_name
   set new_column=value

#1