List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 29 2013 2:04pm
Subject:Re: Update Column in table only if variable is Not NULL
View as plain text  
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" <neil.tompkins@stripped>
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
> >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