• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Move Field values in same table

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). How can I accomplish this by using coldfusion?
0
Errol Farro
Asked:
Errol Farro
  • 3
  • 2
1 Solution
 
LowfatspreadCommented:
?

update yourtable
 set field1 = coalesce(field3,field2)
where ...
0
 
Errol FarroAuthor Commented:
Thanks man, you are indeed a genius. I never heard of the coalesce function.
0
 
Errol FarroAuthor Commented:
Question: Is there a way to consider 0 as a not-null expression? It works fine when the value is Null but not when value is 0. Please advice.
0
 
Errol FarroAuthor Commented:
Sorry for the Typo:

Question: Is there a way to consider 0 also as a null expression? It works fine when the value is Null but not when value is 0. Please advice.
0
 
LowfatspreadCommented:
update yourtable
 set field1 = coalesce(nullif(field3,0),field2)
where ...


nullif ( a,b) returns null if a and b are equal...  else a
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now