MySQL Edit Syntax
Posted on 2014-01-09
I have a table called 'charges'
I want to update the value of field 'calculated_weight' based on the contents of 'department_id', which will determine what to multiply against 'quantity' to determine the update value for 'calculated_weight'. Example:
UPDATE `charges` SET `calculated_weight` = IF(`department_id` IS 4120, 1, 2);
Now, that isn't want I want, but puts into logical form the paragraph above it *almost.
What I really want is to insert (quantity*.0857) into `calculated_weight` when `department_id` == 4120.
'department_id' == 4250, then i would want (quantity*1.0147) to insert into 'calculated_weight'
I have about 6 departmentIDs to monitor and adjust the mutlplication value for.
I tried a few options in MySQL, but it isn't working out for me.