?
Solved

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

Posted on 2007-03-22
2
Medium Priority
?
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

800 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