MSSQ7 - How apply the new default value to old rows retrospectively?

[thainjm]

So, I've created default value called dbo.blank and set it to ' ' . All NULLS in new rows I enter are replaced by this, but not NULLS that already existed before I created the default value.

How can I force an update of the older rows?

Thanks,

Jason.

 

[clapag22]

Write an update statement?

update table_name
set field_name = ''
where field_name is null


Or am I missing something?

 

[pfontyn]

You could set the default value for the column and change the table structure to not allow NULLs.

If the column is still NULLABLE SQL will allow the NULLs to remain.

 

[osjohnm]

Hi thainjm

You should rather use an alter statement to change or add a default and then specify the WITH VALUES keywords which will apply the default value to the existing rows:

ALTER TABLE [TABLE1]
ADD CONSTRAINT [DF_TABLE1_COLUMN1] DEFAULT ''FOR [COLUMN1]
WITH VALUES
GO

If you did it like this...

ALTER TABLE [TABLE1]
ADD CONSTRAINT [DF_TABLE1_COLUMN1] DEFAULT ''FOR [COLUMN1]
GO

..then only new rows would get the default but the existing ones would still be null.

hope that helps

John

 

[MeanGreen]

Did you guys notice when this was first posted? I am sure that by now Jason has found a solution to this issue.

Good Luck!