[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Selecting 1st non-null / non-zero value from list of fields (sql/coldfusion)

Posted on 2007-03-22
2
Medium Priority
?
277 Views
Last Modified: 2013-12-16
I have a table with 3 fields, field1, field2 and field3. I need to write an update query that moves field2 to field1 AND if field3 is entered, move field3 to field1 (replacing value just moved from field2).
I got the following solution from Lowfatspread:

update yourtable
 set field1 = coalesce(field3,field2)
where ...

which is fine when field=null. When field=0, this field is returned. I want to be able to return first non-null / non-zero value in list. Can someone help me out with this? Thanks. Errol.
0
Comment
Question by:Errol Farro
2 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 1000 total points
ID: 18774088
update yourtable
  set field1 = coalesce(case when field3=0 then null else field3x end, field2)
0
 

Author Comment

by:Errol Farro
ID: 18774433
Thanks a lot, it worked.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
Suggested Courses

640 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