I am trying to get a value from a row in one table that matches one value and matches or selects next closet lower value. See table examples and I have provided a way to do it one row at a time.

Table Vendor Cost

Table Price Breaks before update

Table Price Breaks after update

My update statement so far

Update #StandardPList Set UnitCost = VC.Cost
From #StandardPList
  Inner Join 
      (
        Select PartNum, Cost
        From #VendorCost
        Where PartNum = @PartNum
        And BreakQty = (
                Select Top 1 Max(BreakQty) As NextLowBrk 
                From #VendorCost
                Where PartNum = @PartNum And BreakQty < @Qty
                )
      ) VC On #StandardPList.PartNum = VC.PartNum And #StandardPList.Quantity = @Qty

CodePudding user response：

You don't need a join. Just update the price break table and use a correlated subquery to get the cost for the maximum quantity that is less or equal the price break quantity.

update #standardplist
set unitcost = 
(
  select top(1) vc.cost
  from #vendorcost vc
  where vc.partnum = #standardplist.partnum
  and vc.breakqty <= #standardplist.quantity
  order by vc.breakqty desc
);

Usually, this is something you shouldn't do though. The cost is already available in your database and can be queried whenever needed. By storing it again in the price list table you introduce redundancy. This is something we avoid in databases, because redundancy can easily lead to inconsistencies.

If, however, the vendore cost table is subject to change, but the standard price list costs are to remain fixed, then this will no longer be redundancy, as the stored cost can be different from the current vendor costs. I will have no objections then.