14

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
  • 5,070
  • 8
  • 48
  • 70
Rampriya Rajendran
  • 171
  • 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

9 Answers9

28

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
  • 114,394
  • 18
  • 182
  • 210
3

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
  • 326
  • 3
  • 12
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,915
  • 11
  • 36
  • 70
Nikhil Kulkarni
  • 31
  • 2
1

I think below SQL useful to you

update table_name set newly_added_column_name = value;
eebbesen
  • 5,070
  • 8
  • 48
  • 70
Venkatesh Panabaka
  • 2,064
  • 4
  • 19
  • 27
0
   update table_name
   set new_column=value
david sam
  • 521
  • 1
  • 8
  • 25
0
Update  table_name set column_name = value where 'condition';
Community
  • 1
  • 1
Vrushabh
  • 9
  • 1
-1

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

update emp set Comm=5000
Zoe
  • 27,060
  • 21
  • 118
  • 148
sachin yadav
  • 1
-1

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
  • 637
  • 6
  • 17
vinay kumar
  • 9
-1

simply use update query for inserting values into new column .

syntax: update databasename.tablename set columnname=value where columnname=value;

eg. update company.emp set emp_no=8728483892 where ename="manasi";

manasi
  • 1
  • 1