MySQL Error 1093 - Can't specify target table for update in FROM clause (two joins)

Question

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);

Answer 1

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))

Answer 2

Use a subquery for sales_order_parts. mysql then treats as new temporary table

Like

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 (SELECT * FROM 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);