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.gr...@oracle.com>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 > >
