SQL update query using joins

Question

I have to update a field with a value which is returned by a join of 3 tables.

Example:

select     im.itemid     ,im.sku as iSku     ,gm.SKU as GSKU     ,mm.ManufacturerId as ManuId     ,mm.ManufacturerName     ,im.mf_item_number     ,mm.ManufacturerID 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 

I want to update the mf_item_number field values of table item_master with some other value which is joined in the above condition.

How can I do this in MS SQL Server?

Answer 1

UPDATE im SET mf_item_number = gm.SKU --etc FROM item_master im JOIN group_master gm     ON im.sku = gm.sku  JOIN Manufacturer_Master mm     ON gm.ManufacturerID = mm.ManufacturerID WHERE 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 A SET foo = B.bar FROM TableA A JOIN TableB B     ON A.col1 = B.colx WHERE ...