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