13

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?

eebbesen
  • 4,946
  • 8
  • 48
  • 68
Rampriya Rajendran
  • 161
  • 1
  • 1
  • 3
  • What values would you like to place there? Can you show your table's structure and the result you're trying to get? – Mureinik Aug 04 '15 at 11:15
  • share your table structure and value your trying put – Panther Aug 04 '15 at 11:16
  • What you need could be this http://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server – StackTrace Aug 04 '15 at 11:16

8 Answers8

24

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;
  1. 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;
GolezTrol
  • 111,943
  • 16
  • 178
  • 202
3

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 :

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

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

Sinto
  • 3,790
  • 11
  • 36
  • 62
Nikhil Kulkarni
  • 31
  • 2
2

I think below SQL useful to you

update table_name set newly_added_column_name = value;
eebbesen
  • 4,946
  • 8
  • 48
  • 68
Venkatesh Panabaka
  • 1,946
  • 3
  • 17
  • 27
2

Why don't you use UPDATE statement:

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

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

enter image description here

I can update column NewTestColumn by this statement:

UPDATE [dbo].[Table] SET [NewTestColumn] = 'Some value'
Klaudiusz bryjamus
  • 316
  • 3
  • 12
1
   update table_name
   set new_column=value
david sam
  • 525
  • 1
  • 7
  • 25
0

suppose emp is the table and Comm is the new column then fire the below query .

update emp set Comm=5000
Zoe stands with Ukraine
  • 25,310
  • 18
  • 114
  • 149
sachin yadav
  • 1
0
Update table table_name set column_name = value where 'condition';

I preferred to use p.key column for best result.

Dave
  • 4,794
  • 16
  • 30
  • 38
Vrushabh
  • 1
0

For Microsoft SQL (T-SQL):

UPDATE TABLE_NAME SET COLUMN_NAME=10;

here 10 means it will set all values by default to 10

Meow Meow
  • 621
  • 6
  • 16
vinay kumar
  • 11