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.