Update with + Operation

[newphpcoder]

Hi..

 

I had encountered problem in my update 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.

 

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

[The Little Guy]

use ifnull, not nullif

[newphpcoder]

Using COALESCE solve my problem.

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