• As Jeff hinted, you need to be very careful when you don't have one-to-one relationships between your tables. The UPDATE ... FROM syntax can cause unreported cardinality errors. This means that where there is more than one value in your source table matching the row you want to update in your target table, you really don't know which of those values will be used. Worse still, you won't know when this happens because there is no error message returned.

    You have two options to combat this. First, you can use the ANSI SQL syntax (UPDATE ... FROM is proprietary to T_SQL). Your query will look something like the one below. Thanks to Joe Celko for showing me this, although I don't guarantee that it's absolutely correct since I have nothing to test it against for your situation.

    UPDATE Part

    SET Part_Cost

    = (SELECT i.Vendor_Cost

    FROM Inventory i

    JOIN Part_Supplier s

    ON p.partid = s.partid

    WHERE Part.Part_Id = Part_Supplier.Part_Id)

    WHERE EXISTS

    (SELECT *

    FROM Inventory i

    JOIN Part_Supplier s

    ON p.partid = s.partid

    WHERE Part.Part_Id = Part_Supplier.Part_Id)

    Your other option is to use the MERGE statement, which first became available in SQL Server 2008. I'll let you work out the syntax for yourself.

    Even if your relationships are all one-to-one, I would still advise against using UPDATE ... FROM. This is in case a junior developer looks at your code and thinks it's OK to use the same construction in all circumstances. It also gets you into the habit of doing correctly every time.

    John