mwendecker7686 (9/26/2011)
I am I using the Update statement correctly here? I have a table called Part (PK =PartId)which is tied to a supplier in a table named Part_Supplier (PK =SupplierId, FK =PartId) which is tied to a table called Inventory (PK =InventoryId FK =PartId). I want to set Part_Cost that is in the Part table to a Vendor cost in the Inventory table. Would this update statement be correct?UPDATE PART
SET Part_Cost = I.Vendor_Cost
FROM Part_Supplier PS
INNER JOIN Inventory I on I.Part_Id = PS.Part_Id
Thanks for your help...or assurance 🙂
No. It might look right and may even work right, but it isn't right. I know... I've just confused the heck out of you. 😛 In UPDATES with a join or two, you absolutely MUST include the target table (PART, is this case) in the from clause or you could get what is known as the "Halloween Effect". This is were the optimizer makes a huge mistake and goes into a sort of "double loop" mode that strongly resembles a Cartesian Product (Cross Join in SQL terms) and a sub-second update may suddenly take minutes and a 6 second update may suddenly take hours slamming many CPU's into the wall for performance in the process.
Also, your query actually does form a full blown cross join... there is no criteria joining the PART table to the other tables. All the rows in the PART table will be updated to the same value for Part_Cost in the PART table without regard to the actual Part_ID.
As for the actual functionality of the query, I have to ask... since you may have more than one supplier for a given part and each supplier may have more than one cost for the same part never mind that each supplier may have a different cost, what will you use to identify which supplier and cost to use for your update?
To get better help on this problem, you should probably read the first link in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.