Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Creating Records Where There Are None2 - The Sequel 6 27
How to share SSIS Package? 6 37
Increment column based of a FK 8 22
SQL Count issue 24 16
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 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