How to use if then else in UPDATE Statement

Databases
1

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

2