So here's what I've got:
I have a table full of the number of parts ordered (Part) and another table with the items that have been ordered (OrderLine):
Now, I need to get a sum of all the items that are in OrderLine that have "PartNum" and update the Allocation field of "Part" to this value in a single SQL statement.
I've been at this for about 5 hours now and can't seem to figure it out. Initially, I thought this should work:
SET Allocation = (SELECT SUM(Temp.NumOrdered)
FROM OrderLine AS Temp
WHERE Temp.PartNum = Part.PartNum)
WHERE Part.PartNum = 'KV29'
But this kicks back telling me that it's not an "updatable query".