Updating multiple columns in a single update transaction

Posted on 2013-12-10
Last Modified: 2013-12-10
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?
Question by:ccleebelt
LVL 21

Expert Comment

by:Dale Burrell
ID: 39710310
update table1 set
  column1 = case when column1 = 0 then null else column1 end
  , column2 = case when column2 = 0 then null else column2 end
LVL 24

Accepted Solution

chaau earned 500 total points
ID: 39710548
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


Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now