updating multiple columns to 0

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 multiple columns to 0

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2014-04-25 : 09:06:27

Hello guys,
I have a large table with many columns that have rows with N/A or null values, which are gotten from a bulk insert.

In order to format the data properly, we need to convert the data from N/A or null to 0, as the original values come from the supplier.

In my code the best way to achieve this is running multiple queries as

update csv_testing set testing5 = 0 where testing5 like'%N/A%'
update csv_testing set testing6 = 0 where testing6 like'%N/A%'
update csv_testing set testing7 = 0 where testing7 like'%N/A%'
update csv_testing set testing9 = 0 where testing9 like'%N/A%'

etc for about 12 columns

is there a better way of doing this ?

many thanks
M

_____________________

Yes O !

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-25 : 09:13:07

try this


update csv_testing
set testing5 = case when testing5 like '%N/A%' then '0' when testing5 IS NULL then '0' else testing5 end,
    testing6 = case when testing6 like '%N/A%' then '0' when testing6 IS NULL then '0' else testing6 end,
    testing7 = case when testing7 like '%N/A%' then '0' when testing7 IS NULL then '0' else testing7 end
where
(testing5 IS NULL or testing5 like '%N/A%') or
(testing6 IS NULL or testing6 like '%N/A%') or
(testing7 IS NULL or testing7 like '%N/A%')

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

mary_itohan
Posting Yak Master

191 Posts

Posted - 2014-04-26 : 10:22:21

thanks a lot, much appreciated

_____________________

Yes O !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources