List:General Discussion« Previous MessageNext Message »
From:hsv Date:October 29 2013 2:30pm
Subject:Re: Update Column in table only if variable is Not NULL
View as plain text  
>>>> 2013/10/28 21:23 +0000, 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.

Thread
Update Column in table only if variable is Not NULLNeil Tompkins28 Oct
  • Re: Update Column in table only if variable is Not NULLAndy Wallace28 Oct
  • Re: Update Column in table only if variable is Not NULLShawn Green28 Oct
    • Re: Update Column in table only if variable is Not NULLNeil Tompkins28 Oct
      • Re: Update Column in table only if variable is Not NULLMichael Dykman29 Oct
      • Re: Update Column in table only if variable is Not NULLShawn Green29 Oct
        • Re: Update Column in table only if variable is Not NULLhsv30 Oct
          • Re: Update Column in table only if variable is Not NULLShawn Green30 Oct
            • Re: Update Column in table only if variable is Not NULLNeil Tompkins30 Oct
              • Re: Update Column in table only if variable is Not NULLShawn Green30 Oct
      • Re: Update Column in table only if variable is Not NULLhsv29 Oct