SQL UPDATE with JOIN for WHERE Clause

Question

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

possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) — Elias, Jul 31 '14 at 14:44
Be careful of scalar functions. They can be serious performance issues. — Sean Lange, Jul 31 '14 at 14:44

score 4 · Accepted Answer · edited Jul 31 '14 at 14:53

4

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

edited Jul 31 '14 at 14:53

Gordon Copestake

1,616
4
21
37

answered Jul 31 '14 at 14:43

Becuzz

6,846
26
39

That seems to have done ALL records, not just that that are Category 1 – Gordon Copestake Jul 31 '14 at 14:49
Ahh, ignore me, I need to join against b.ComponentItemID not b.ItemID but thats fine. Thanks Becuzz – Gordon Copestake Jul 31 '14 at 14:51

SQL UPDATE with JOIN for WHERE Clause

1 Answers1