update table colum with default value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

update table colum with default value

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-10-01 : 17:36:37

Hi all,

I need to update my table columns to a default value. Beside get into the Management Studio to updated every column...does anyone have a script to updated it?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-01 : 17:45:09

Do you mean adding a default constraint to the columns or updating the data directly?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 17:45:41

what do you mean?
do you have a table like
sometable
col1 int
col2 datetime
col3 varchar(255)
and want to update every col3 to 'default_whatever' only one time?
Or do you want to something other?

Webfred

Planning replaces chance by mistake

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 17:46:51

sorry tara - you're too fast 4 me

Planning replaces chance by mistake

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 17:48:04

maybe it's because youre mother and i am already grandpa ;o)

Planning replaces chance by mistake

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-01 : 18:06:27

ok - this is my grandson:
http://www.youtube.com/watch?v=fIVNK8-VYBo

Planning replaces chance by mistake

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-01 : 18:20:15

What a cute outfit! And a messy bedroom!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hai
Yak Posting Veteran

84 Posts

Posted - 2008-10-01 : 23:55:49

Hi Tara,

yes...adding a default constraint to the columns if is null then set it to 0 for int and blank for varchar.

thanks

quote:
Originally posted by tkizer

Do you mean adding a default constraint to the columns or updating the data directly?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-02 : 03:57:12

quote:
Originally posted by hai

Hi Tara,

yes...adding a default constraint to the columns if is null then set it to 0 for int and blank for varchar.

thanks

quote:
Originally posted by tkizer

Do you mean adding a default constraint to the columns or updating the data directly?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

create default constraint using ALTER TABLE ALTER COLUMN
ALTER TABLE yourtablename ALTER COLUMN yourintcolumnname int NOT NULL DEFAULT 0
ALTER TABLE yourtablename ALTER COLUMN yourvarcharcolumnname varchar(30) NOT NULL DEFAULT 'Default Value'
...

then during inserts either dont include then in insert list at all or use

INSERT INTO yourtable (othercolumns, intcol,varcharcol)
VALUES( values for others..,DEFAULT,DEFAULT)

and for updates

UPDATE yourtable
SET intcol=DEFAULT,
varcharcol=DEFAULT
...

and if you dont want to explicitly include them in update then you need a update trigger to update default values automatically as default constraints will automatically applied only on inserts and not in updates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources