Updating multiple columns in a single update transaction

I have a table with many columns.  Some have a default value of 0 and need to set it to null (after removing the constraint of course).

Rather than have multiple update statements that look like this:

update table1 set column1 = null where column1 = 0
update table1 set column2 = null where column2 = 0

Is there a way to wrap these up into a single update statement or transaction with multiple where clauses?
ccleebeltPresidentAsked:
Who is Participating?
 
chaauCommented:
I would perhaps add a WHERE clause to restrict the number of scanned rows, like this:
update table1 set
  column1 = case when column1 = 0 then null else column1 end
  , column2 = case when column2 = 0 then null else column2 end 
WHERE column1 = 0 OR column2 = 0

Open in new window

0
 
Dale BurrellDirectorCommented:
update table1 set
  column1 = case when column1 = 0 then null else column1 end
  , column2 = case when column2 = 0 then null else column2 end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.