Posted By: Anonymous
I have to update a field with a value which is returned by a join of 3 tables.
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?
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
UPDATE A SET foo = B.bar FROM TableA A JOIN TableB B ON A.col1 = B.colx WHERE ...