Solved

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

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.

733 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