mysql - Update row based on multiple relations -

i have 3 tables:

• kits (kit_id, kit_weight)
• kit_components (kit_id, quantity, component_id)
• components (component_id, weight)

for each entry in kits table there can 1 or more kit_component entries. each component has weight column can either weight or null if haven't weighed yet. need run sql query update weight column of kits table based on total weight times quantity of components or if of weights null set value null i'm not sure possible, it?

note: i'd avoid scripts, triggers or procedures. have code when component saved or kit updated i'd able in bulk.

---

edit: further clarify can sum weights * quantity doesn't deal component rows being null null acts 0 in sum (i've tested this)

e.g. kit1 has 1xcomponenta weight of 14 , 2xcomponentb weight of null

select kit_id, sum(component.weight * kit_component.quantity) kit_component inner join component on kit_component.component_id = component.id group kit_component.kit_id

this return 14 kit1, wrong because componentb has no weight instead should return null.

hugo kornelis: "if data in group (as formed group by) has nulls , non-null data, nulls ignored , result sum of remaining numbers: sum {1, 3, null, 5} = sum {1, 3, 5} = 9 if data in group null, nulls ignored well, leaving no rows summed @ all: result sum of empty set; definition null. sum {null, null} = sum {} = null."

based on edit, problems seem to make following query return null when value going null:

select kit_id, sum(component.weight * kit_component.quantity) kit_component inner join      component      on kit_component.component_id = component.id group kit_component.kit_id 

you can additional logic:

select kit_id,        (case when count(component.weight) = count(*) ,                   count(component.quantity) = count(*)              sum(component.weight * kit_component.quantity)         end) kit_component inner join      component      on kit_component.component_id = component.id group kit_component.kit_id 

remember count(<field>) counts number of non-null values in field. so, counts saying "all values non-null" or, equivalently, "no values null".