April 20, 2015 at 1:12 am
Hi There,
I want to set a default value to a column while adding it.
Create table test(id int )
insert into Test values(123)
alter table test add column2 int default(0)
In the above query I have set the default value, but it will not set the default value to the already inserted columns.
So how to set a default value for the already inserted rows, while adding a new column to it ?
April 20, 2015 at 1:19 am
vignesh.ms (4/20/2015)
Hi There,I want to set a default value to a column while adding it.
Create table test(id int )
insert into Test values(123)
alter table test add column2 int default(0)
In the above query I have set the default value, but it will not set the default value to the already inserted columns.
So how to set a default value for the already inserted rows, while adding a new column to it ?
The default value is used for new rows where no other value is specified. If you want to update existing rows, use an update statement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2015 at 1:58 am
Use "WITH VALUES" in your default specification:
alter table test add column2 int default(0) WITH VALUES
-- Gianluca Sartori
April 20, 2015 at 2:15 am
spaghettidba (4/20/2015)
Use "WITH VALUES" in your default specification:
alter table test add column2 int default(0) WITH VALUES
Would this overwrite existing non-null values, as that appears to be the requirement?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2015 at 3:07 am
No values will be in the column, since it's being added by the ALTER TABLE command. What do you mean exactly?
-- Gianluca Sartori
April 20, 2015 at 3:12 am
spaghettidba (4/20/2015)
No values will be in the column, since it's being added by the ALTER TABLE command. What do you mean exactly?
Oops. It's Monday morning and I've clearly not had enough coffee.:blush:
For some bizarre reason, I was thinking that the requirement was to add a default constraint to an existing column and blitz any existing values in that column.
Your first answer nails it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2015 at 3:12 am
I think he's confused his requirement: he mentions "already inserted rows" and "already inserted columns" almost in the same breath. I think Gianluca's solution will work for him; alternatively he could specify NOT NULL when adding the column.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply