Update Column in table only if variable is Not NULL
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
Re: Update Column in table only if variable is Not NULL
Try: update my_table set fieldname1 = Now(), Fieldname2 = :myVariable where Fieldname3 is not null On 10/28/13 11:06 AM, 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 -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code. - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Update Column in table only if variable is Not NULL
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
RE: Change to MySQL Community Server 5.7.2?
2013/10/25 00:08 +, Rick James There's an old saying, If it ain't broke, don't fix it. Why _might_ 5.6.x or 5.7.x be better for you? Sure there might be some features you might want, might be some performance improvements that you might notice, etc. And there might be some regressions that will bite you. Fortunately, regressions are rare. You should probably upgrade to 5.6 soon, simply to avoid having to do a double upgrade when you eventually go to 5.7. Everyone wants the computer where the database is changed to a newer: good time for installing a newer MySQL, too. In 5.6 there is a feature of interest to me: DATETIME (I wish it were DATE) also allows DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP; and in 5.7 a bug that I reported, relevant to my code, was amended. Now, in the announcement, it is called public milestone release of MySQL 5.7: what is that, and how different from generally available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Update Column in table only if variable is Not NULL
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.comwrote: 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
