SQL Update query with condition from a third table

Question

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

You seem to know the filter that needs to be applied so not understanding your struggle..do you have a specific issue? — P.Salmon, Apr 17 '22 at 12:13

score 1 · Answer 1 · edited Apr 17 '22 at 11:24

1

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

edited Apr 17 '22 at 11:24

marc_s

732,580
175
1,330
1,459

answered Apr 17 '22 at 11:13

Mohamed Hosny

11
3

score 1 · Answer 2 · answered Apr 17 '22 at 11:13

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;

SQL Update query with condition from a third table

2 Answers2