update field from another select query

i am stuck with a simple query if someone can help me on on the same. i want to update one field with query from another table the table structure is as follows:- table stockmain - fields - itemcode, avgcost table sales - fields - itemid, saleprice, costprice(this field is to be generated with query from stockmain table(avgcost field) the query is follows:-

$qry = "UPDATE sales SET costprice = SELECT avgcost FROM st开发者_Python百科ockmain WHERE itemcode = 'sales.itemid' ";

---

You can join these two tables to get one data set, and then copy data from one field to another, e.g. -

UPDATE sales sl
  JOIN stockmain stm
    ON stm.itemcode = sl.itemid
SET sl.costprice = stm.avgcost;