Update Default Value Of A Field In SQL Table.

[rookery]

Can anyone tell me how I'd change / update the default value of a field in a SQL table. eg. from 'Hello' to 'Goodbye'?

I tried running the ADD DEFAULT command but I got an error saying that a constraint was already bound to that field which is correct.

 

[Clandon]

Try this:

update [tablename]
set [fieldname]='Goodbye'
where [fieldname]='Hello'

 

[rookery]

Thanks for the reply Clandon but thats not quite right.

I'm looking to update the default value of that field from what it currently is 'Hello' to 'Goodbye'.

Any ideas?

 

[SQLSister]

A deafult value is a constraint. First you need to drop the existing constraint, then create a new one using the Alter Table command. See Alter Table in BOL for details.

Questions about posting. See faq183-874

 

[dbomrrsm]

Hello

go to EM an locate the table - right click on it and choose design table - locate the field and at the bottom of the page you should see the old default value of Helo - changer this to Goodbye

Goodbye !

[blue]DBomrrsm[/blue]

 

[rookery]

Ta for your replies. DBomrrsm I need to do it by script and not the easy way....unfortunately!!

I will try BOL Sis.

 

[2ni]

You must drop your constraint before adding a new one :

ALTER TABLE dbo.<table name>
DROP CONSTRAINT <constraint name>

go
ALTER TABLE dbo..<table name> ADD CONSTRAINT
<constraint name> DEFAULT 'GoodBye' FOR <column name>

if you ignore the constraint name you can do this :

declare @tablename varchar(255)
declare @colname varchar(255)
declare @constraintname sysname
declare @sql nvarchar(255)

set @tablename = '<your table name>'
set @colname='<your column name>'

select @constraintname=sysobjects.name
from sysconstraints
inner join syscolumns on sysconstraints.colid=syscolumns.colid
inner join sysobjects on sysobjects.id = sysconstraints.constid
where syscolumns.name=@colname and sysobjects.parent_obj = (select id from sysobjects where xtype='U' and name=@tablename)


print @constraintname