I'm trying to update the unit_price in a sales_order_parts detail table with calculation from the applied_discount field from sales_orders. Also, the price is in the master_part_list table as price. When I run from the select statement down, it runs fine and returns the list of values from order_number 209 with the correct calculation. When I run it complete from the update line, it returns "Error Code: 1093. You can't specify target table 'sop' for update in FROM clause" Any ideas?
update sales_order_parts as sop
set unit_price =
(select (master_part_list.price * (1-(so.applied_discount/100)))
from sales_orders as so
inner join sales_order_parts as sop2
on so.id = sop2.order_id
inner join master_part_list
on sop2.part_id = master_part_list.id
where so.order_number = 209);
You could try with a join without subquery
update sales_order_parts as sop
INNER JOIN sales_orders as so on so.id = sop.order_id
AND so.order_number = 209
inner join master_part_list on sop.part_id = master_part_list.id
SET sop.unit_price = master_part_list.price * (1-(so.applied_discount/100))