Recursive sql with cursors or left joined sql tables

Question

Hello,

I am currently building a system which tracks a number of things, among these things is a parameter that indicates the status of a particular object, such as an order.

From my readings, it appeared that using a recursive sql with cursors, was the way to go, but just running this on the two tables I have, takes about 40 seconds, which is not too good, considering my sample data is substantially smaller than the real-world counterpart.

I did some tests on a left join statement, and its much faster in getting the results, but I cant quite figure out how to update a statusfield, with a particular value, based on three requirements.

Basically, Im testing to see if an order is within the appropriate interval for due delivery, if the amount delivered is within an appropiate interval, and if it was delivered to the correct place, each of these will result in a number, adding those number together will give a number that goes into the statusfield of the orderline.

This is pretty simple with a recursive call, because you evaluate each line one at a time, but I cant figure out how to update each line, using just a left join, because each line has to evaluated seperately.


I think this makes sense, but if not, please let me know and I can perhaps draw up a better example.

I have the sql-code that captures all the lines that need to have their statusfield updated, but Im just having problems figuring out how to update the statusfield with the correct number.

Any ideas on how to do this?