Consider having the following tables. (Microsoft RMS) -Item
-id
-cost
-SupplierList
-itemID
-supplierCost
when using RMS, You have an Item, it has it’s own cost… maybe that cost is an average cost, or something like that, and that item may have multiple suppliers, and each supplier has an itemcost associated with it, this sucks with reporting and margins in RMS, because when you update the cost of the item, it doesnt update the supplier cost, because it doesnt know how to do so…


UPDATE supplierlist
SET cost =
CASE
WHEN (select id from item where id = itemid) = itemid
THEN (select cost from item where id = itemid)
else cost
END

this query updates TWO tables from a base table… in our example it updates all of the suppliers cost, in the items table… it’s pretty cool