SQL update query using joins

Question

A value that is returned from a join of three tables must be updated in a field.

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 replace a value that is joined in the aforementioned condition with the value from the mf_item_number column of the item_master database.

How can I do this in MS SQL Server?

Answer 1

Use this:

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 be explicit, the FROM clause's table alias can be referred to in the UPDATE clause. Thus, 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 ...

I hope this helps you.