Re: Update Column in table only if variable is Not NULL

Shawn Green Tue, 29 Oct 2013 08:40:35 -0700

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 acolumn-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

Reply via email to