SQL Update query with condition from a third table-CodePudding

I need to update a column in a table based on other tables.

SELECT        
    StockItem.ItemID, StockItem.Code, StockItem.AverageBuyingPrice, 
    MovementBalance.CostPrice, ProductGroup.CostingMethodID, 
    CostingMethod.CostingMethodName
FROM
    StockItem 
INNER JOIN
    MovementBalance ON StockItem.ItemID = MovementBalance.ItemID 
INNER JOIN
    ProductGroup ON StockItem.ProductGroupID = ProductGroup.ProductGroupID 
INNER JOIN
    CostingMethod ON ProductGroup.CostingMethodID = CostingMethod.CostingMethodID

Basically, what I'm trying to achieve is to set

MovementBalance.CostPrice = StockItem.AverageBuyingPrice

where CostingMethod.CostingMethodID = 2.

I'm struggling with the CostingMethod as it comes from a different table.

Could someone help me out?

Many thanks

CodePudding user response：

On the assumption since you've tagged Sql server Management Studio you're using SQL Server, the following would appear to be what you are asking for. Note the use of table aliases makes for a more compact and readable query:

update mb 
  set mb.CostPrice = si. AverageBuyPrice
from StockItem si
join MovementBalance mb ON mb.ItemID = si.ItemID
join ProductGroup pg on pg.ProductGroupID = si.ProductGroupID
join CostingMethod cm ON  cm.CostingMethodID = pg.CostingMethodID
where cm.CostingMethodId = 2;

CodePudding user response：

UPDATE MovementBalance
SET CostPrice = StockItem.AverageBuyingPrice
FROM StockItem 
INNER JOIN MovementBalance ON StockItem.ItemID = MovementBalance.ItemID 
INNER JOIN ProductGroup ON StockItem.ProductGroupID = ProductGroup.ProductGroupID 
INNER JOIN CostingMethod ON ProductGroup.CostingMethodID = 
CostingMethod.CostingMethodID
WHERE CostingMethod.CostingMethodID = 2