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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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