In SQL, How to add values after add a new column in the existing table?

 

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?

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 : 

    ALTER TABLE YourTable ALTER COLUMN YourNewColumn NOT NULL;

    Why don't you use UPDATE statement: 

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

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

    I can update column NewTestColumn by this statement: 

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

       update table_name
   set new_column=value
    链接地址: http://www.djcxy.com/p/24718.html

    上一篇: 在非空列上插入后触发

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