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