any one help this. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

any one help this.

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2008-01-10 : 04:39:12

I have table with tow columns.
One column has some null values.now i want to delete the null values and set some default value.
up to this my table has 10 records

how i replane null values with defalt values?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-10 : 04:47:32

update tbl set col = 'val' where col is null

alter table tbl add constraint mycon default 'val' for col
That will give the default value in future if the column isn't referenced in the insert.
It will still allow null values to be insertd explicitely though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

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

It involves multiple steps:

-- Firstly convert existing null values to some default value
Update tbl1
set col = 0 -- assuming null column is int
where col is null

-- create default
create default d1 as 0

-- now bind default to the column
Exec sp_bindefault d1, 'tbl1.col'

-- make column non null
alter table tbl1
alter column col int not null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

raky
Aged Yak Warrior

767 Posts

Posted - 2008-01-10 : 04:50:23

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources