Updating Nulls to No Help - 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

Updating Nulls to No Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-13 : 05:55:45

Hey guys

Quick question
Aim- update Nulls to ‘no’

This is my query but i am keep returning (0 row(s) affected) when i know there is around 100,000 rows which needs to be changed

Update #Mason
set Clientline = 'No'
where Clientline = 'Null'

Would appreciate any help available

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-13 : 05:57:35

quote:
Originally posted by masond

Hey guys

Quick question
Aim- update Nulls to ‘no’

This is my query but i am keep returning (0 row(s) affected) when i know there is around 100,000 rows which needs to be changed

Update #Mason
set Clientline = 'No'
where Clientline = 'Null'

Would appreciate any help available

where Clientline is null

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-13 : 05:58:41

HA webfred

i just worked it out

the code is
update #Mason
set Clientline = 'No'
where Clientline is null

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-09-13 : 06:01:44

HOw do you default Nulls to blank

divya.ce
Starting Member

16 Posts

Posted - 2013-09-13 : 06:21:27

When you create columns in your table, add a default constraint as '' to that column, or always check for null entries in your where clause as "WHERE ISNULL(ClientName ,'') ='' "
ISNULL will check the column's value, if NULL then change it to blank or whatever you give as the second parameter.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 06:27:43

update #Mason
set Clientline = 'No'
where NULLIF(Clientline, '') is null --- If you have blank for NULL column

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-13 : 07:57:27

see this to understand:

create table #Mason (Id int identity(1,1),  Clientline varchar(255), test varchar(255))
go

insert #Mason(test)
select 'first test without default'

select * from #Mason

go


alter table #Mason add constraint Clientline_value default 'No' for Clientline
go

insert #Mason(test)
select 'second test with default'

select * from #Mason

go

drop table #Mason

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources