August 26, 2008 at 12:07 am
Hi PPl,
In my scenario, I need to alter a table and add a new column to it. This column will be an INT type column with a DEFAULT value as 0.
My question is, that is it possible to have the default value in all the underlying rows?
Refer to the scripts below:
create table #a
(a int)
insert into #a select 1
insert into #a select 1
insert into #a select 1
insert into #a select 1
insert into #a select 1
alter table #a
add b int default 0
select * from #a
the output that I am currently getting is:
a b
1 null
1 null
1 null
the expected output is:
a b
1 0
1 0
1 0
Please suggest.
August 26, 2008 at 12:29 am
Defaults only apply to new rows inserted. If you want the existing rows with null to change to that value, you'll have to run an update statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 1:13 am
GilaMonster (8/26/2008)
Defaults only apply to new rows inserted. If you want the existing rows with null to change to that value, you'll have to run an update statement.
NAH Man, I fixed it, there is a key word WITH VALUES. Use it with the alter statement and it will apply the default value to the existing rows as well.
August 26, 2008 at 1:35 am
Yeah, that works. Sorry, I misread your question, thought you were adding a default to an existing column.
The WITH VALUES is only valid if you're adding a new column with a default, not a default on a existing column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 1:42 am
Actually I found the solution just after posting the question. Then I tried to delete the post a couple of times but was unable. :hehe:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply