Re: Update Column in table only if variable is Not NULL

2013-10-30 Thread Shawn Green
Hi Neil, On 10/30/2013 9:55 AM, Neil Tompkins wrote: Shawn What I need is that if I pass say 10 parameters/variables to a query, I only want to update the column/field if the value passed is NOT NULL. On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green wrote: Hi, On 10/29/2013 9:52 PM, h...@tbbs

Re: Update Column in table only if variable is Not NULL

2013-10-30 Thread Neil Tompkins
Shawn What I need is that if I pass say 10 parameters/variables to a query, I only want to update the column/field if the value passed is NOT NULL. On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green wrote: > Hi, > > On 10/29/2013 9:52 PM, h...@tbbs.net wrote: > >> 2013/10/29 11:35 -0400, Shawn Green

Re: Update Column in table only if variable is Not NULL

2013-10-29 Thread Shawn Green
Hi, On 10/29/2013 9:52 PM, h...@tbbs.net wrote: 2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 re

Re: Update Column in table only if variable is Not NULL

2013-10-29 Thread hsv
2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 reflects the attempt to change, not change. -

Re: Update Column in table only if variable is Not NULL

2013-10-29 Thread hsv
2013/10/28 21:23 +, Neil Tompkins Basically the snippet of the UPDATE statement I provided shows updating only 1 field. However in my live working example, I have about 20 possible fields that "might" need to be updated if the variable passed for each field is NOT NULL. We

Re: Update Column in table only if variable is Not NULL

2013-10-29 Thread Shawn Green
Hello Neil, On 10/28/2013 5:23 PM, Neil Tompkins wrote: Hi Shawn Thanks for your reply. Maybe my example wasn't detailed enough. Basically the snippet of the UPDATE statement I provided shows updating only 1 field. However in my live working example, I have about 20 possible fields that "mi

Re: Update Column in table only if variable is Not NULL

2013-10-29 Thread Michael Dykman
Consider: update table1 set field1 = if( :var,:var,field1), ... Can be in a procedure but doesn't have to be. On Oct 28, 2013 5:28 PM, "Neil Tompkins" wrote: > Hi Shawn > > Thanks for your reply. Maybe my example wasn't detailed enough. Basically > the snippet of the UPDATE statement I provide

Re: Update Column in table only if variable is Not NULL

2013-10-28 Thread Neil Tompkins
Hi Shawn Thanks for your reply. Maybe my example wasn't detailed enough. Basically the snippet of the UPDATE statement I provided shows updating only 1 field. However in my live working example, I have about 20 possible fields that "might" need to be updated if the variable passed for each fiel

Re: Update Column in table only if variable is Not NULL

2013-10-28 Thread Andy Wallace
Try: update my_table set fieldname1 = Now(), Fieldname2 = :myVariable where Fieldname3 is not null On 10/28/13 11:06 AM, Neil Tompkins wrote: Hi If I have a update statement like UPDATE MY_TABLE SET FieldName1 = Now(), FieldName2 = :MyVariable WHERE FieldName3 = 'Y' How can I only update t

Re: Update Column in table only if variable is Not NULL

2013-10-28 Thread Shawn Green
Hello Neil, On 10/28/2013 2:06 PM, Neil Tompkins wrote: Hi If I have a update statement like UPDATE MY_TABLE SET FieldName1 = Now(), FieldName2 = :MyVariable WHERE FieldName3 = 'Y' How can I only update the FieldName2 field if the value of MyVariable is NOT NULL ? Thanks Neil This needs t

Update Column in table only if variable is Not NULL

2013-10-28 Thread Neil Tompkins
Hi If I have a update statement like UPDATE MY_TABLE SET FieldName1 = Now(), FieldName2 = :MyVariable WHERE FieldName3 = 'Y' How can I only update the FieldName2 field if the value of MyVariable is NOT NULL ? Thanks Neil