CODEWITHSUNDEEP
sqlsql-servert-sql
Yofi
Yofi

Reputation: 47

How to reference the same column multiple times with UPDATE -- SQL Server Mgmt Studio 2012

I have a database which includes recipes for specific items. I'd like to create an update statement which updates all item costs based on the costs of the ingredients in their recipe. The problem I believe I am dealing with is that the value 'cost' for both the ingredients and the items for which they are used are located in the same table. I have managed to write a statement which updates the cost for only ONE item, but I can't seem to make this work for ALL items that have a recipe. Attached is the functional statement for ONE item and the statement I tried to compose for ALL items.

This is the statement which returns the correct values, but only applies to ONE specific item number.

UPDATE inventory
SET inventory.cost = (SELECT SUM(inventory_ingredients.quantity * inventory.cost)
                      FROM inventory_ingredients, inventory
                      WHERE inventory.itemnum = inventory_ingredients.ingredient
                        AND inventory_ingredients.itemnum = 'BeesKnees1200'
                      GROUP BY inventory_ingredients.itemnum)
WHERE inventory.itemnum = 'BeesKnees1200'

and this is the statement I was trying to compose which would update all Inventory.Cost with that SUM

UPDATE inventory 
SET inventory.cost = (SELECT SUM(inventory_ingredients.quantity * inventory.cost)
                      FROM inventory_ingredients, inventory
                      WHERE inventory.itemnum = inventory_ingredients.ingredient
                      GROUP BY inventory_ingredients.itemnum)
WHERE inventory.itemnum = Inventory_ingredients.itemnum

In this scenario, I get a "could not be bound" on inventory_ingredients.itemnum

I think the problem here is that I'm referencing the same column twice, but I'm not sure how to correct it. Any ideas would be really appreciated.

The attached image shows relevant columns for the tables Inventory and Inventory_Ingredients respectively.

Upvotes: 0

Views: 104

Answers (2)

Ilyes
Ilyes

Reputation: 14928

What if you change your query to

;WITH CTE AS
(
SELECT SUM(ISNULL(TRY_CAST(II.quantity AS INT), 1) * I.cost) * 1.0000 S,
       II.itemnum
FROM inventory_ingredients II JOIN inventory I ON I.itemnum = II.ingredient
GROUP BY II.itemnum
)
UPDATE inventory 
SET inventory.cost = CTE.S
FROM inventory JOIN CTE ON inventory.itemnum = CTE.itemnum;

Upvotes: 2

Ehsan
Ehsan

Reputation: 785

Try this piece of code and let me know if it works for you:

UPDATE inventory 
    SET inventory.cost = (SELECT SUM(inventory_ingredients.quantity*inventory.cost)
    FROM inventory_ingredients II, inventory I
    WHERE I.itemnum=II.ingredient and i.itemnum = inventory.itemnum 
    GROUP BY II.itemnum)

Upvotes: 0

Related Questions