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

Question

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?

Answer 1

Two solutions.

1. 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;

2. 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;

Answer 2

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'