CODEWITHSUNDEEP
sqlsql-server
Gordon Copestake
Gordon Copestake

Reputation: 1647

SQL UPDATE with JOIN for WHERE Clause

Hopefully an easy question: I have two sql tables Items and BillOfMaterials

Items has fields ItemID and ItemCategory BillOfMaterials has fields ItemID and ComponentItemID

How can I do an UPDATE on BillOfMaterials to change the ComponentItemID where the ItemID has a certain category? e.g.

UPDATE BillOfMaterials
SET ComponentItemID = dbo.GetNewItemID(ComponentItemID)
WHERE ItemCategory = 1 <-- Magic join here to pull in ItemCategory

Upvotes: 0

Views: 55

Answers (1)

Becuzz
Becuzz

Reputation: 6866

This should do it:

UPDATE b
SET ComponentItemID = dbo.GetNewItemID(ComponentItemID)
FROM BillOfMaterials b
INNER JOIN Items I on I.ItemID = b.ComponentItemID
WHERE i.ItemCategory = 1

Upvotes: 4

Related Questions