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.tompk...@googlemail.com> 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.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 > > > > >
