SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 updating multiple columns to 0
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

191 Posts

Posted - 04/25/2014 :  09:06:27  Show Profile  Reply with Quote
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


Yes O !

Flowing Fount of Yak Knowledge

8780 Posts

Posted - 04/25/2014 :  09:13:07  Show Profile  Visit webfred's Homepage  Reply with Quote
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
(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.
Go to Top of Page

Posting Yak Master

191 Posts

Posted - 04/26/2014 :  10:22:21  Show Profile  Reply with Quote
thanks a lot, much appreciated


Yes O !
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000