Re: Update Column in table only if variable is Not NULL
Shawn What I need is that if I pass say 10 parameters/variables to a query, I only want to update the column/field if the value passed is NOT NULL. On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green shawn.l.gr...@oracle.comwrote: Hi, On 10/29/2013 9:52 PM, h...@tbbs.net wrote: 2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 reflects the attempt to change, not change. The way I understood the second explanation was like this. He wants to update a row of data. The FieldName1 field is always updated to the current date and time. If any of the new values (passed in via variables) are not NULL for a specific column, replace the value on the row with the new value otherwise maintain the current value. He may yet mean something completely different than how I read it the second time. -- 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: Update Column in table only if variable is Not NULL
Hi Neil, On 10/30/2013 9:55 AM, Neil Tompkins wrote: Shawn What I need is that if I pass say 10 parameters/variables to a query, I only want to update the column/field if the value passed is NOT NULL. On Wed, Oct 30, 2013 at 3:41 AM, Shawn Green shawn.l.gr...@oracle.comwrote: Hi, On 10/29/2013 9:52 PM, h...@tbbs.net wrote: 2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 reflects the attempt to change, not change. The way I understood the second explanation was like this. He wants to update a row of data. The FieldName1 field is always updated to the current date and time. If any of the new values (passed in via variables) are not NULL for a specific column, replace the value on the row with the new value otherwise maintain the current value. He may yet mean something completely different than how I read it the second time. Then we agree on your intentions. The COALESCE() construction I demonstrated will work just fine for you. -- 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: Update Column in table only if variable is Not NULL
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
Re: Update Column in table only if variable is Not NULL
Hello Neil, On 10/28/2013 5:23 PM, Neil Tompkins 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 mailto: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 My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) -- 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: Update Column in table only if variable is Not NULL
2013/10/28 21:23 +, Neil Tompkins 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. Well, maybe something as loathsome as this: UPDATE T SET F1 = NOW(), F2 = IFNULL(@F2, F2), FF3 = IFNULL(@FF3, FF3), FF4 = IFNULL(@FF4, FF4), F5 = IFNULL(@F5, F5), but if all are NULL F1 will be misleading. I can think only of NOT (@F2 IS NULL AND @FF3 IS NULL AND @FF4 IS NULL AND @F5 IS NULL ) or making F1 a variable that takes ON UPDATE CURRENT_TIMESTAMP: I have read that MySQL checks every UPDATE for actual change, and only then changes such an F1 when something actually else changes. -- 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
2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 reflects the attempt to change, not change. -- 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, On 10/29/2013 9:52 PM, h...@tbbs.net wrote: 2013/10/29 11:35 -0400, Shawn Green My favorite technique is the COALESCE function for this on a column-by-column basis SET FieldName1 = Now(), FieldName2 = COALESCE(:MyVariable, FieldName2) but if MyVariable is NULL, FieldName1 reflects the attempt to change, not change. The way I understood the second explanation was like this. He wants to update a row of data. The FieldName1 field is always updated to the current date and time. If any of the new values (passed in via variables) are not NULL for a specific column, replace the value on the row with the new value otherwise maintain the current value. He may yet mean something completely different than how I read it the second time. -- 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
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: 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
