Alter Table / Default

[rmatthew]

I am trying to alter a column within a table and set the default value to 0:

 

I am using SQL Server 2000

 

here is what I am using (it gives 'incorrect syntax near DEFAULT')

 

alter table chronology alter column oper_id numeric(10,0) set default 0 not null

[Nerseus]

Your syntax is close, but a bit off. At least, it's off if you're using SQL Server:

alter table chronology alter column oper_id numeric(10,0) not null DEFAULT 0 

At least, that's the syntax when using "add" column, not sure if you can set a default when altering a column. I would think so, but not positive. If not, you can always do a mass update followed by an alter that sets the column to NOT NULL.

 

-ner

[rmatthew]

This works (as you stated) for an add, but no such luck on an alter column.

Unsure what you mean by mass update and then alter (without loosing data).

[Nerseus]

Yep, I read the help more and see there's no option to set a default for an existing column that would update the data.

 

Assuming you've already got the column in the table but want to change to not null, do the mass update before altering the table/column:

UPDATE chronology SET oper_id = 0 WHERE oper_id IS NULL
GO -- The GO is optional
alter table chronology alter column oper_id numeric(10,0) not null

 

-ner