Updating Null values - 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 Null values

Author

Topic

atmonline
Starting Member

14 Posts

Posted - 2008-06-23 : 19:40:16

how to use update statement to update fields in a table that has Null values

update sheet1 set [Middle Initial]='' where isnull([Middle Initial],'')=''
this statement some times works sometimes not.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 19:45:03

WHERE [Middle Initial] IS NULL

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

Subscribe to my blog

atmonline
Starting Member

14 Posts

Posted - 2008-06-23 : 19:50:13

I tried that sql .But i a getting message 0 rows affected

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 20:10:55

Then you don't have any null data in that column.

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-06-23 : 23:43:32

quote:
Originally posted by atmonline

I tried that sql .But i a getting message 0 rows affected

probably you're having ' ' values rather than null values. try this too

update sheet1 set [Middle Initial]=''
WHERE isnull(nullif([Middle Initial],' '),'')=''

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-24 : 00:02:53

Or perhaps the word null instead of null data:

WHERE [Middle Initial] = 'null'

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

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 09:29:41

quote:
Originally posted by tkizer

Or perhaps the word null instead of null data:

WHERE [Middle Initial] = 'null'

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

Subscribe to my blog

Probably. I have seen some newbies storing the string 'NULL' instead of NULL

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 09:34:55

quote:
Originally posted by visakh16

quote:
Originally posted by atmonline

I tried that sql .But i a getting message 0 rows affected

probably you're having ' ' values rather than null values. try this too

update sheet1 set [Middle Initial]=''
WHERE isnull(nullif([Middle Initial],' '),'')=''

You can also use

WHERE isnull([Middle Initial],'')<=''

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources