SQL update query using joins

UPDATE imSET mf_item_number = gm.SKU --etcFROM item_master imJOIN group_master gm    ON im.sku = gm.sku JOIN Manufacturer_Master mm    ON gm.ManufacturerID = mm.ManufacturerIDWHERE im.mf_item_number like 'STA%' AND      gm.manufacturerID = 34

To make it clear... The UPDATE clause can refer to an table alias specified in the FROM clause. So im in this case is valid

Generic example

UPDATE ASET foo = B.barFROM TableA AJOIN TableB B    ON A.col1 = B.colxWHERE ...

Adapting this to MySQL -- there is no FROM clause in UPDATE, but this works:

UPDATE    item_master im    JOIN    group_master gm ON im.sku=gm.sku     JOIN    Manufacturer_Master mm ON gm.ManufacturerID=mm.ManufacturerIDSET    im.mf_item_number = gm.SKU --etcWHERE    im.mf_item_number like 'STA%'    AND    gm.manufacturerID=34

One of the easiest way is to use a common table expression (since you're already on SQL 2005):

with cte as (select    im.itemid    ,im.sku as iSku    ,gm.SKU as GSKU    ,mm.ManufacturerId as ManuId    ,mm.ManufacturerName    ,im.mf_item_number    ,mm.ManufacturerID    , <your other field>from     item_master im, group_master gm, Manufacturer_Master mm where    im.mf_item_number like 'STA%'    and im.sku=gm.sku    and gm.ManufacturerID = mm.ManufacturerID    and gm.manufacturerID=34)update cte set mf_item_number = <your other field>

The query execution engine will figure out on its own how to update the record.