List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:October 30 2013 2:15pm
Subject:Re: Update Column in table only if variable is Not NULL
View as plain text  
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 <shawn.l.green@stripped>wrote:
>
>> Hi,
>>
>> On 10/29/2013 9:52 PM, hsv@stripped 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 reflects the attempt to change, not
>>> change.
>>>
>>>
>>>
>> The way I understood the second explanation was like this.
>>
>> He wants to update a row of data. The FieldName1 field is always updated
>> to the current date and time.  If any of the new values (passed in via
>> variables) are not NULL for a specific column, replace the value on the row
>> with the new value otherwise maintain the current value.
>>
>> He may yet mean something completely different than how I read it the
>> second time.
>>

Then we agree on your intentions.  The COALESCE() construction I 
demonstrated will work just fine for you.

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Update Column in table only if variable is Not NULLNeil Tompkins28 Oct
  • Re: Update Column in table only if variable is Not NULLAndy Wallace28 Oct
  • Re: Update Column in table only if variable is Not NULLShawn Green28 Oct
    • Re: Update Column in table only if variable is Not NULLNeil Tompkins28 Oct
      • Re: Update Column in table only if variable is Not NULLMichael Dykman29 Oct
      • Re: Update Column in table only if variable is Not NULLShawn Green29 Oct
        • Re: Update Column in table only if variable is Not NULLhsv30 Oct
          • Re: Update Column in table only if variable is Not NULLShawn Green30 Oct
            • Re: Update Column in table only if variable is Not NULLNeil Tompkins30 Oct
              • Re: Update Column in table only if variable is Not NULLShawn Green30 Oct
      • Re: Update Column in table only if variable is Not NULLhsv29 Oct