I have to update a table but some of the fields are calculated and require several joins.
These are the involved tables:
client(id, name, markup)
raw_material(id, description, cost)
operator(id, name, cost)
machine(id, description, cost)
forecast(id, client, raw_material, raw_qty, raw_cost, operator1_hours, operator2_cost, operator2_hours, operator2_cost, machine, machine_hrs, machine_cost, markup, profit, total)
In practice, to calculate a cost I have to multiply a field from the update table times the field of a corresponding table.
Here is the code, which does not work, but you should be able to get the idea:
Code:
update Forecast set raw_cost=raw_material.cost*raw_qty,
operator1_cost=operator.cost*operator1.hours where operator.id=1,
operator2_cost=operator.cost*operator2.hours where operator.id=2,
machine_cost=machine.cost*machine.hours where machine.id=forecast.machine,
markup=client.markup where client.id=forecast.client,
profit=sum((raw_cost; operator1_cost; operator2_cost; machine_cost))*markup,
total=sum((raw_cost; operator1_cost; operator2_cost; machine_cost))*(1+markup)
where forecast.id=[input from user]
How can I change the update query so that it will work? thanks a lot.