using sub selects with SQL UPDATE

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

Leave a comment