I have two tables - let's call them dbo.ValuesToReduce and dbo.Reserve The data in the first table (dbo.ValuesToReduce) is:
The second table (dbo.Reserve) looks like this
What I need to do is: update the Values in ValuesToReduce table using the latter table of Reserves, reducing the numbers until the reserve supply is exhausted. Here's what I should get after running the script:
Here is a set based approach.
answered Oct 15, 2009 at 04:23 PM
I don't think this is possible in a truly set based manner - because the fact that you have more than one PartnerID value in the ValuesToReduce table makes it an iterative process. Is there any way that you can re-factor such that PartnerID is unique in the values to reduce table? As that would make the problem much much simpler...
If not, then the way I would attack it is actually with a cursor (because it is an iterative process in it's nature). In fact, I would use two cursors, with logic roughly equivalent to:
edit -> I really hope someone comes along with a set-based solution, however. I'd learn a lot from that.
answered Oct 14, 2009 at 10:05 AM
Matt Whitfield ♦♦
An interesting question, but I don't think you can do it with as a set-based solution. Even if you could, I'm not sure you would want to. Any solution you could come up with will almost certainly look a little crazy to the next guy that's got to support it.
Most old-school db guys would have already passed this off as something you should handle in your application rather than in the database. I don't exactly believe in hard-line rules, but in this case I think you either have to use your application or some cursors/loops to get the functionality you're looking for.
answered Oct 14, 2009 at 03:52 PM
Thanks guys. I ended up writing something similar to Andomar's solution here http://stackoverflow.com/questions/1564917/reducing-values-in-one-table-until-reserves-depleted-in-another-recursion
I'll try to optimize it and fiddle with it some more when I have more time, since it's an interesting problem.
answered Oct 15, 2009 at 03:50 AM