Update with multiple columns - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Update with multiple columns

Author

Topic

supersql
Yak Posting Veteran

99 Posts

Posted - 2008-08-05 : 18:24:19

Update Site
SET
Site.CorpOHPer,sp.CorpOHPer=isnull(sp.CorpOHPer,''),
Site.DivOHPer,sp.DivOHPer=isnull(sp.DivOHPer,'')
Site.RegOHPer=sp.RegOHPer,
Site.BurdenPer=sp.BurdenPer
from StagingSiteProfile sp

WHERE Site.SiteID=sp.SiteID

I am using ISNULL so that if there is a null value for that column I want to replace it by blank, buti get 0 here instead blank.

is there a way i can ignore update if there is a null??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-05 : 23:48:15

quote:
I am using ISNULL so that if there is a null value for that column I want to replace it by blank, buti get 0 here instead blank.

The datatype of that column is an integer ? It will be converted to 0 if you assign '' to it

quote:
is there a way i can ignore update if there is a null??

yes


update s
set    CorpOHPer = case when sp.CorpOHPer is not null then sp.CorpOHPer else s.CorpOHPer end,
       DivOHPer  = case when sp.DivOHPer  is not null then sp.DivOHPer  else s.DivOHPer  end,
       .  .  .
from   Site s inner join StagingSiteProfile sp
on     s.SiteID = sp.SiteID

KH
[spoiler]Time is always against us[/spoiler]

supersql
Yak Posting Veteran

99 Posts

Posted - 2008-08-06 : 15:00:42

data type is not integer but it is a float.

CorpOHPer = case when sp.CorpOHPer is not null then sp.CorpOHPer else s.CorpOHPer end

from this though WHEN condition satisfied or not but still it will replace s.CorpOHPer

but i want to ignore update if its NULL. may be this will do

CorpOHPer = case when sp.CorpOHPer is not null then sp.CorpOHPer end

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-06 : 15:03:22

WHERE Site.SiteID=sp.SiteID
and not sp.CorpOHPer is null
and not sp.DivOHPer is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources