Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Update with + Operation

    Hi..

    I had encountered problem in my update code:

    Code:
    UPDATE kanban_checker_doz SET
    virtual_doz = (count_doz_chemical_weighing + count_doz_compounding + count_doz_extrusion
    + count_doz_forming);
    it did not work when I have only data in count_doz_chemical_weighing and the other is NULL.

    I also tried this code but still the virtual_doz = NULL.

    Code:
    UPDATE kanban_checker_doz SET
    virtual_doz = ((NULLIF(count_doz_chemical_weighing, 0)) + (NULLIF(count_doz_compounding, 0)) + (NULLIF(count_doz_extrusion, 0))
    + (NULLIF(count_doz_forming, 0)));
    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am getting really tired of the identical thread on two different sites

    i'm leaving this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try using COALESCE:

    There is a subtle difference between IFNULL and NULLIF. Have a look at this NULLIF versus IFNULL IT Integrated Business Solutions
    Code:
    UPDATE kanban_checker_doz SET
    virtual_doz = COALESCE(count_doz_chemical_weighing, 0) + COALESCE(count_doz_compounding, 0) + COALESCE(count_doz_extrusion, 0)
    + COALESCE(count_doz_forming, 0);
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    Yes, using COALESCE solve my problem.
    Code:
    update kanban_checker_doz
      set kanban_doz = coalesce(count_doz_deflashing, 0), 
      virtual_doz = coalesce(count_doz_chemical_weighing, 0) +
                        coalesce(count_doz_compounding,0) +
                        coalesce(count_doz_extrusion,0) +
                        coalesce(count_doz_forming,0),
      total_doz = coalesce(count_doz_chemical_weighing, 0) +
                coalesce(count_doz_compounding, 0) +
                coalesce(count_doz_extrusion, 0) +
                coalesce(count_doz_forming, 0) +
                coalesce(count_doz_deflashing, 0)
    Thank you
    Last edited by newphpcoder; 03-13-12 at 21:14.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •