List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:October 29 2013 3:35pm
Subject:Re: Update Column in table only if variable is Not NULL
View as plain text  
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 "might" need to be updated if the variable
> passed for each field is NOT NULL.
>
> Therefore, I felt this needs to be done at database level in the stored
> procedure.  How can I accomplish this.
>
> Thanks
> Neil
>
>
> On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green <shawn.l.green@stripped
> <mailto:shawn.l.green@stripped>> wrote:
>
>     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 to be a decision you make at the application level to not
>     execute the UPDATE command in the first place. Not every decision
>     needs to be made by the database. Plus, it will save you the time of
>     a full network round trip just to get a result from the server that
>     you affected 0 rows (parsing, optimizing, executing).
>
>     Now, if this was just a typo and your :MyVariable was meant to be
>     @MyVariable (a MySQL user variable) then you can put that test in
>     the WHERE clause of the command
>
>     UPDATE MY_TABLE
>     SET FieldName1 = Now(), FieldName2 = @MyVariable
>     WHERE FieldName3 = 'Y'  AND @MyVariable IS NOT NULL
>
>     --
>     Shawn Green
>     MySQL Senior Principal Technical Support Engineer
>     Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
>     Office: Blountville, TN
>
>     --
>     MySQL General Mailing List
>     For list archives: http://lists.mysql.com/mysql
>     To unsubscribe: http://lists.mysql.com/mysql
>

My favorite technique is the COALESCE function for this on a 
column-by-column basis

SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2)

-- 
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