sql - 使用联接的SQL更新查询(SQL update query using joins)

Question

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

(我必须更新一个值，该值由3个表的联接返回。)

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.

(我想用其他在上述条件下加入的值更新表item_master的mf_item_number字段值。)

How can I do this in MS SQL Server?

(如何在MS SQL Server中执行此操作？)

  ask by Shyju translate from so

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.

(为了清楚起见... UPDATE子句可以引用FROM子句中指定的表别名。)

So im in this case is valid

(所以im在这种情况下是有效的)

Generic example (通用示例)

UPDATE A
SET foo = B.bar
FROM TableA A
JOIN TableB B
    ON A.col1 = B.colx
WHERE ...