Solved

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

Posted on 2007-03-22
2
263 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 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is format f12.8 for a CSV file 6 41
Add '#' to end of file 2 30
How to use Full Text CONTAINS with Case in SQL 6 20
sql server group by every 2hrs 5 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

19 Experts available now in Live!

Get 1:1 Help Now