how to use if then else in UPDATE Statement

[newphpbees]

Hi,

 

I have table: plan which has fields : PCODE, max_lot, min_lot

sample data:

PCODE = P35

max_lot = 5

min_lot  = 3

 

PCODE = P35M

max_lot = 5

min_lot  = 3

 

and table kanban_checker : PCODE, count_wip_chemicalweighing, count_wip_compounding, count_wip_extrusion, count_wip_forming, count_wip_deflashing, virtual, kanban and kanban_status....

 

I have this code of update to update kanban_checker table fields except kanban_status.

 

here is my code that I want to add update for kanban_status:

 

UPDATE kanban_checker kc SET count_wip_chemical_weighing = (SELECT COUNT(NULLIF(wip_chemicalweighing, 0)) AS count_wip_chemical_weighing FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE), 
count_wip_compounding = (SELECT COUNT(NULLIF(wip_compounding, 0)) AS count_wip_compounding FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
count_wip_extrusion = (SELECT COUNT(NULLIF(wip_extrusion, 0)) AS count_wip_extrusion FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
count_wip_forming = (SELECT COUNT(NULLIF(wip_forming, 0)) AS count_wip_forming FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
count_wip_deflashing = (SELECT COUNT(NULLIF(wip_deflashing, 0)) AS count_wip_deflashing FROM kanban_data kd WHERE kd.PCODE = kc.PCODE GROUP BY kc.PCODE),
virtual = (SELECT ((count(NULLIF(kd.wip_chemicalweighing, 0))) + (count(NULLIF(kd.wip_compounding, 0))) + (count(NULLIF(kd.wip_extrusion, 0))) + (count(NULLIF(kd.wip_forming, 0))) + (count(NULLIF(kd.wip_deflashing, 0)))) AS virtual FROM kanban_data kd WHERE kc.PCODE = kd.PCODE GROUP BY kc.PCODE);

 

I need to check the max_lot and min_lot per PCODE in table plan for kanban_status..

IF virtual is >= max_lot then kanban_status = 'MAX' elseif virtual is <= min_lot then kanban_status  = 'MIN'.

 

IS it possible?HOw?

 

Thank you

 

 

[fenway]

You really need to convert that to a multi-table join.