List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:October 28 2013 9:23pm
Subject:Re: Update Column in table only if variable is Not NULL
View as plain text  
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>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
>
>

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