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>
> 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
> Therefore, I felt this needs to be done at database level in the stored
> procedure. How can I accomplish this.
> On Mon, Oct 28, 2013 at 6:17 PM, Shawn Green <shawn.l.green@stripped
> > 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
> > be made by the database. Plus, it will save you the time of a full
> > 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
> > 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